topic retain or lag? in SAS Programming

retain or lag?

mac2 — Tue, 09 May 2017 11:48:44 GMT

Hello,

i have the data below and i want for each change for the variable Type (by US) to get the first date and keep only one record for each type:

data test;
informat date YYMMDD10. ;format date ddmmyy10.;
input @1 US $ @3 Type $ @5 date date7.;
datalines;
X A 30JAN09
X C 31MAR09
X C 31MAY09
X A 30JUN09
X C 30AUG09
X P 31OCT09
X A 01NOV09
X A 21NOV09
X C 20DEC09
X C 31DEC09
Y A 30JUN09
Y C 30AUG09
Y C 28FEB10
Y A 31MAY10
;

the result should be:

US	Type	date
X	C	20-Dec-09
X	A	1-Nov-09
X	P	31-Oct-09
Y	C	30-Aug-09
Y	A	31-May-10

Thank you

Re: retain or lag?

RW9 — Tue, 09 May 2017 11:53:50 GMT

You don't need either reatin or lag. Sort your data so the record occurs first, then output that. Note your required output does not match your test data. For X, A, the lowest date is 30JAN09, you highlight 01NOV09.

data test;
  informat date YYMMDD10.  ;format date ddmmyy10.;
  input @1 US $ @3 Type $  @5 date date7.;
datalines;
X A 30JAN09
X C 31MAR09
X C 31MAY09
X A 30JUN09
X C 30AUG09
X P 31OCT09
X A 01NOV09
X A 21NOV09
X C 20DEC09
X C 31DEC09
Y A 30JUN09
Y C 30AUG09
Y C 28FEB10
Y A 31MAY10
;
run;

proc sort data=test out=want;
  by us type date;
run;
data want;
  set want;
  by us type;
  if first.type then output;
run;

Re: retain or lag?

mac2 — Tue, 09 May 2017 12:12:28 GMT

sorry, i wasn/t explicit enough. i want for each US and type to have the 'first' date when the type changed.

for example, for US='X' type='C' i want to keep only the records underlined:

X A 30JAN09
X C 31MAR09
X C 31MAY09
X A 30JUN09
X C 30AUG09
X P 31OCT09
X A 01NOV09
X A 21NOV09
X C 20DEC09
X C 31DEC09

Re: retain or lag?

Tom — Tue, 09 May 2017 12:16:10 GMT

Use the NOTSORTED keyword on the BY statement. Sort by US DATE but set the data by US TYPE NOTSORTED.

data want ;
  set have ;
  by us type notsorted;
  if first.type;
run;

Re: retain or lag?

RW9 — Tue, 09 May 2017 12:17:25 GMT

Edit, i forgot about the date part. This should work assuming data is sorted by date also.

I mean from your logic this works:

data want;
  set test;
  by us type notsorted;
  if first.type and not first.us then output;
run;