topic Re: Comparing rows add iteration num in SAS Programming

Comparing rows add iteration num

JediApprentice — Mon, 20 Aug 2018 20:03:13 GMT

I'm looking to compare rows in the following way (assume dataset is sorted by FullCode, ChangeDt). Note: FullCode is simply ID concatenated with Type.

01 | 03 | Y | T | 08Aug1998 | 09Jul1998 | 0103

02 | 03 | Y | T | 08Aug1998 | 09Jul1998 | 0203

03 | 07 | O | T | 01May1994 | 13Jun1996 | 0307

03 | 07 | O | T | 20Jan1998 | 13Jun1996 | 0307

03 | 07 | N | T | 16Apr1999 | 13Jun1996 | 0307

So I want to create an iterator ID to count as we go down the rows per FullCode, when the ChangeDt is different, start at 1 and count up until you hit the next FullCode. Like below:

01 | 03 | Y | T | 08Aug1998 | 09Jul1998 | 0103 | 1

02 | 03 | Y | T | 08Aug1998 | 09Jul1998 | 0203 | 1

03 | 07 | O | T | 01May1994 | 13Jun1996 | 0307 | 1

03 | 07 | O | T | 20Jan1998 | 13Jun1996 | 0307 | 2

03 | 07 | N | T | 16Apr1999 | 13Jun1996 | 0307 | 3

Re: Comparing rows add iteration num

PeterClemmensen — Mon, 20 Aug 2018 20:13:48 GMT

Do something like this

data have;
input ID Type Source $ Name $ (ChangeDt StartDt)(:date9.) FullCode;
format ChangeDt StartDt date9.;
infile datalines dlm='|';
datalines;
01|03|Y|T|08Aug1998|09Jul1998|0103
02|03|Y|T|08Aug1998|09Jul1998|0203
03|07|O|T|01May1994|13Jun1996|0307
03|07|O|T|20Jan1998|13Jun1996|0307
03|07|N|T|16Apr1999|13Jun1996|0307
;

data want;
   set have;
   by FullCode ChangeDt;
   if first.FullCode then Count=1;
   else if ChangeDt ne lag1(ChangeDt) then Count+1;
run;

Re: Comparing rows add iteration num

novinosrin — Mon, 20 Aug 2018 20:44:29 GMT

data have;
input ID Type Source $ Name $ (ChangeDt StartDt)(:date9.) FullCode;
format ChangeDt StartDt date9.;
infile datalines dlm='|';
datalines;
01|03|Y|T|08Aug1998|09Jul1998|0103
02|03|Y|T|08Aug1998|09Jul1998|0203
03|07|O|T|01May1994|13Jun1996|0307
03|07|O|T|20Jan1998|13Jun1996|0307
03|07|N|T|16Apr1999|13Jun1996|0307
;

data want;
if 0 then set have;
do count=1 by 1 until(last.FullCode);
set have;
by  FullCode ChangeDt;
output;
end;
run;