DATA Step, Macro, Functions and more

retain or lag?

Reply
New Contributor
Posts: 2

retain or lag?

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:

USTypedate
XC20-Dec-09
XA1-Nov-09
XP31-Oct-09
YC30-Aug-09
YA31-May-10

 

Thank you

Super User
Super User
Posts: 7,955

Re: retain or lag?

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;

 

New Contributor
Posts: 2

Re: retain or lag?

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

Super User
Super User
Posts: 7,050

Re: retain or lag?

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;
Super User
Super User
Posts: 7,955

Re: retain or lag?

[ Edited ]

 

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;

 

Ask a Question
Discussion stats
  • 4 replies
  • 199 views
  • 0 likes
  • 3 in conversation