BookmarkSubscribeRSS Feed
ycubic
Calcite | Level 5

I have a data set as below.

country codesnapshot date
canada1/3/2014
canada1/4/2014
us1/5/2014
us1/6/2014
us1/7/2014
canada1/8/2014
canada1/9/2014


I would like to create a result table as below.

country codestart snapshot dateend snapshot date
canada1/3/20141/4/2014
us1/5/20141/7/2014
canada1/8/20141/9/2014

I can only create the start snapshot date by rank and join the same table, and am having trouble to get the end snapshot date.

3 REPLIES 3
Ksharp
Super User
data have;
input country $      date : mmddyy10.;
format date mmddyy10.;
cards;
canada     1/3/2014
canada     1/4/2014
us     1/5/2014
us     1/6/2014
us     1/7/2014
canada     1/8/2014
canada     1/9/2014
;
run;
data want;
 set have;
 by country notsorted;
 retain start;
 if first.country then start=date;
 if last.country then do; end=date;output;end;
 drop date;
 format start end mmddyy10.;
run;

Xia Keshan

ycubic
Calcite | Level 5

thank you. i didn't think it this way, the sas way. i think i was too much into the sql way.

Reeza
Super User

The SQL way

proc sql;

create table daily_snapshot as

select country_code, min(date) as start_date, max(date) as end_date

from have

group by country_code

order by country_code;

quit;

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
  • 3 replies
  • 402 views
  • 0 likes
  • 3 in conversation