BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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;

 

IDLotIncludeCodeCombined
5097130  
509712117 
50971116117
35948370  
3594836155 
3594835115855
3594834118855.158
3594833119155.158.188
359483215755.158.188.191
359483116155.158.188.191.57
6543944149 
654394315049
654394212949.50
6543941.0116149.50.29
654394116149.50.29.61
65511861140 
65511851191140
65511841188140.191
6551183157140.191.188
6551182150140.191.188.57
6551181161140.191.188.57.50
65545141140 
65545131188140
6554512157140.188
6554511161140.188.57
264699031140 
264699021158140
264699011188140.158
28135592149 
2813559116149
29763711161 

 

Welcome suggestions!!

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9
thanks. Your code helped me simplify my last line so it works! I did use the lag function for the priorcode but used retain for combination.

This change worked:

/*define combination*/
if include=1 then combination = catx(".", combination, codeprior) ;

View solution in original post

2 REPLIES 2
Avery
Fluorite | Level 6

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="";

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9
thanks. Your code helped me simplify my last line so it works! I did use the lag function for the priorcode but used retain for combination.

This change worked:

/*define combination*/
if include=1 then combination = catx(".", combination, codeprior) ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 564 views
  • 0 likes
  • 2 in conversation