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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.