BookmarkSubscribeRSS Feed
mac2
Calcite | Level 5

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

mac2
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

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;

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 910 views
  • 0 likes
  • 3 in conversation