BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

I need to get for each usubjid i need the first ECSTDTC(start date) and last ECENDTC (end date) so basically one record per usubjid.

 

proc sort data=ec out=ec7; by usubjid ECSTDTC; run;
data ec1;
set ec7;
by usubjid ECSTDTC ECENDTC;
if First.ECSTDTC and last.ECENDTC then output;
run;

 

can anyonew help me

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

That is not how the by variables work. They indicate whether the current record is the first or the last for the group defined by the by variable.  So FIRST.ECSTDTC and LAST.ECENDTC could only be true if there is only one record for that value of ECSTDTC within that value of USUBJID.

 

If your data it properly sorted and has no missing values then you want.

data ec1;
  set ec7;
  by usubjid ;
  retain first_start ;
  if first.usubjid then first_start=ECSTDTC;
  if last.usubjid ;
  last_stop = ECENDTC;
  keep usubjid first_start last_stop ;
run;

If you have missing values then you will need to add more logic and also retain the variable you use to store the last end date.

View solution in original post

11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

If you want to use ECENDTC in the BY statement in your data step, then you should also sort by that variable in your PROC SORT.

vraj1
Quartz | Level 8

i used it but still doesnt work

Astounding
PROC Star

How about:

 

proc summary data=ec7 nway;

class usubjid;

var ecstdtc ecendtc;

output out=want (keep=usubjid start_date end_date) min(ecstdtc)=start_date max(ecendtc)=end_date;

run;

vraj1
Quartz | Level 8

unfortunately i cannot use proc summary as the dates are character dates.

 

 

Astounding
PROC Star

It's easy enough to convert character dates to numeric.  What format are they in? 

 

It would guess that they're in year-month-day form, or else sorting them couldn't help.  But one never knows.  Give a couple of examples.

vraj1
Quartz | Level 8

attaching test data

Astounding
PROC Star

So these variables have both a date and a time.  Do you care about the time portion, or just the date portion?

vraj1
Quartz | Level 8

can usubjid be used in that code and take first and last observation? as i need time as well to calculate duration later

Astounding
PROC Star

It would definitely work (but might be overkill) to process each variable separately:

 

proc sort data=have;

   by usubjid ecstdtc;

run;

data start;

set have;

by usubjid ecstdtc;

if first.usubjid;

keep usubjid ecstdtc;

run;

 

proc sort data=have;

by usubjid ecendtc;

run;

data finish;

set have;

by usubjid ecendtc;

if last.usubjid;

keep usubjid ecendtc;

run;

 

Perhaps you can see the handwriting on the wall here ... your life will be a lot simpler down the road if you start out by converting  those character variables to numeric DATETIMEs, since SAS is built to handle those easily.

Tom
Super User Tom
Super User

That is not how the by variables work. They indicate whether the current record is the first or the last for the group defined by the by variable.  So FIRST.ECSTDTC and LAST.ECENDTC could only be true if there is only one record for that value of ECSTDTC within that value of USUBJID.

 

If your data it properly sorted and has no missing values then you want.

data ec1;
  set ec7;
  by usubjid ;
  retain first_start ;
  if first.usubjid then first_start=ECSTDTC;
  if last.usubjid ;
  last_stop = ECENDTC;
  keep usubjid first_start last_stop ;
run;

If you have missing values then you will need to add more logic and also retain the variable you use to store the last end date.

vraj1
Quartz | Level 8

Thanks a lot Tom.

Can the new date format be converted to date9(characcter) in the same data step?

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
  • 11 replies
  • 2643 views
  • 2 likes
  • 4 in conversation