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
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;
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
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.