concatenating prior values

CP2 — Fri, 19 Aug 2016 17:52:59 GMT

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!

Re: concatenating prior values

Avery — Fri, 19 Aug 2016 18:52:25 GMT

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

Re: concatenating prior values

CP2 — Fri, 19 Aug 2016 19:09:56 GMT

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) ;

topic concatenating prior values in SAS Programming

concatenating prior values

Re: concatenating prior values

Re: concatenating prior values