Scenario :
I have this kind of data
TiliA TiliB
A 1
A 2
A 3
B 1
B 2
I want to get like this
TiliA TiliB
A 1 2 3
B 1 2
How to do it ?
Thanks
Explanation: If we observe; basically user wants to clubb all the values of variable TIliB into single value groupping by variable TIliA. We can use retain statement with first. and last. option for this.
Solution: To use first. and last. option the variable needs to be already sorted. So sorting the dataset by TiliA is the first step. then we will use Data Step with Retain Statement and First. and Last. options.
Its interesting to discuss the use of Retain statement; as we know once SAS compiler comes across Data Step it creates Program Data Vector (PDV) with memory allocation for each variable used in that data step. Initially it assigns missing value to all the variables; These variables get the values assigned to them in due course of Data Step steps. At last after comming across the RUN statement sas compiler again resets the PDV variables to missing values. This applies to each iteration of the code; for each row.
There are many incidents as one in the above scenario; when we need some variables to retain their values across iteration as they can be used in for next rows.
Use of First. and Last. - If the dataset is sorted by any variable then SAS automatically assigns 2 variables to each distinct value of that by variable. If its first occurance of that variable value in the dataset then First. will have value (1 or TRUE) and If its last occurance of that variable value in the dataset then Last. will have value (1 or TRUE)
The solution for the above scenario is demonstrated in the code below;
Code
data t;
infile datalines;
input TiliA $ TiliB $;
datalines;
A 1
A 2
A 3
B 1
B 2
;
run;
data x (drop=TiliB rename=(str=TiliB));
retain TiliA TiliB;
set t;
retain str;
length str $ 100;
by TiliA;
if first.TiliA then str='';
str = catx(' ',str, TiliB);
if last.TiliA then output;
run;
OutPut: