concatenating prior values

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 133
Accepted Solution

concatenating prior values

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!

 


Accepted Solutions
Solution
‎08-19-2016 03:10 PM
Frequent Contributor
Frequent Contributor
Posts: 133

Re: concatenating prior values

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


All Replies
Occasional Contributor
Posts: 9

Re: concatenating prior values

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

Solution
‎08-19-2016 03:10 PM
Frequent Contributor
Frequent Contributor
Posts: 133

Re: concatenating prior values

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) ;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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