Hello,
I am having trouble concatenating prior rows. I have a few stipulations which is confusing me as to how to use the retain .
1) I need to sort by LOT descending.
2) the 'code' of the first reportable id (include=1) needs to be retained so that it can be placed in the following record for the combination variable.
3) subsequent rows need to concatenate code values into the combination column
Here is the sample data and below is what I want the combination column to look like.
data have;
input ID lot Include Code $ ;
infile datalines delimiter=',' missover;
datalines;
50971,3,0,
50971,2,1,17
50971,1,1,61
359483,7,0,
359483,6,1,55
359483,5,1,158
359483,4,1,188
359483,3,1,191
359483,2,1,57
359483,1,1,61
654394,4,1,49
654394,3,1,50
654394,2,1,29
654394,1.01,1,61
654394,1,1,61
655118,6,1,140
655118,5,1,191
655118,4,1,188
655118,3,1,57
655118,2,1,50
655118,1,1,61
655451,4,1,140
655451,3,1,188
655451,2,1,57
655451,1,1,61
2646990,3,1,140
2646990,2,1,158
2646990,1,1,188
2813559,2,1,49
2813559,1,1,61
2976371,1,1,61
;
run;
proc sort data=have;
by id descending lot;
run;
data want;
format codeprior combination $30.;
set have;
by id descending lot ;
retain combination ;
codeprior = lag(code) ;
/*initialize to missing at the start of each id*/
/*cannot use as else statement because first id could be include = 0 */
if first.id then do;
codeprior = "";
combination="" ;
end ;
/*define combination*/
if include=1 and codeprior ne "" then combination=codeprior ;
else if include =1 then combination = catx(".", combination, codeprior) ;
run;
ID | Lot | Include | Code | Combined |
50971 | 3 | 0 | ||
50971 | 2 | 1 | 17 | |
50971 | 1 | 1 | 61 | 17 |
359483 | 7 | 0 | ||
359483 | 6 | 1 | 55 | |
359483 | 5 | 1 | 158 | 55 |
359483 | 4 | 1 | 188 | 55.158 |
359483 | 3 | 1 | 191 | 55.158.188 |
359483 | 2 | 1 | 57 | 55.158.188.191 |
359483 | 1 | 1 | 61 | 55.158.188.191.57 |
654394 | 4 | 1 | 49 | |
654394 | 3 | 1 | 50 | 49 |
654394 | 2 | 1 | 29 | 49.50 |
654394 | 1.01 | 1 | 61 | 49.50.29 |
654394 | 1 | 1 | 61 | 49.50.29.61 |
655118 | 6 | 1 | 140 | |
655118 | 5 | 1 | 191 | 140 |
655118 | 4 | 1 | 188 | 140.191 |
655118 | 3 | 1 | 57 | 140.191.188 |
655118 | 2 | 1 | 50 | 140.191.188.57 |
655118 | 1 | 1 | 61 | 140.191.188.57.50 |
655451 | 4 | 1 | 140 | |
655451 | 3 | 1 | 188 | 140 |
655451 | 2 | 1 | 57 | 140.188 |
655451 | 1 | 1 | 61 | 140.188.57 |
2646990 | 3 | 1 | 140 | |
2646990 | 2 | 1 | 158 | 140 |
2646990 | 1 | 1 | 188 | 140.158 |
2813559 | 2 | 1 | 49 | |
2813559 | 1 | 1 | 61 | 49 |
2976371 | 1 | 1 | 61 |
Welcome suggestions!!
Thanks!
Have you thought about using lag(X) which just grabs the value from the previous row of the the variable x. You have to think about how best to sort your data but if its sorted in the right order then you could do something like. Note first.id is true at the first occurence of a unique id in a data set which is sorted by id.
lagid=lag(id);
lagcode=lag(code);
lagcombined=lag(combined);
if first.id then
do;
lagcombined="";
lagcode="";
end;
if id=lagid then combined=lagcombined||lagcode;
else combined="";
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.