BookmarkSubscribeRSS Feed
NewUsrStat
Pyrite | Level 9

Hi guys, 

suppose to have the following: 

 

data have;
  input ID $ (Start End) (:date.) Place $;
  format start end date9.;
cards;
0001 13JAN2015 20JAN2015 0
0001 21JAN2015 31DEC2015 1
0001 01JAN2018 31DEC2018 0
0001 01JAN2019 31DEC2019 1
0002 01JAN2015 31DEC2015 1
0002 01JAN2019 31OCT2019 0
0002 01NOV2019 31DEC2020 0
;

Is there a way to add two variables (with 1 = yes, 0= no): one indicating the first date the patient has Place = 1 ever in his life and another indicating the first date the patient has Place = 0 ever in his life? 

Desired output: 

 

data have;
  input ID $ (Start End) (:date.) Place First_1 First_0 $;
  format start end date9.;
cards;
0001 13JAN2015 20JAN2015 0 0  1
0001 21JAN2015 31DEC2015 1 1  0
0001 01JAN2018 31DEC2018 0 0  0
0001 01JAN2019 31DEC2019 1 0  0
0002 01JAN2015 31DEC2015 1 1  0
0002 01JAN2019 31OCT2019 0 0  1
0002 01NOV2019 31DEC2020 0 0  0
;

Thank you in advance

2 REPLIES 2
ballardw
Super User
data have;
  input ID $ (Start End) (:date.) Place $;
  format start end date9.;
cards;
0001 13JAN2015 20JAN2015 0
0001 21JAN2015 31DEC2015 1
0001 01JAN2018 31DEC2018 0
0001 01JAN2019 31DEC2019 1
0002 01JAN2015 31DEC2015 1
0002 01JAN2019 31OCT2019 0
0002 01NOV2019 31DEC2020 0
;

proc sort data= have;
   by id place start;
run;

data want;
   set have;
   by id place start;
   first_1 = (first.place and place='1');
   first_0 = (first.place and place='0');
run;

proc sort data=want;
   by id start;
run;

First and/or Last are available with BY group processing in a data step. Each variable on the BY has its own indicator accessed using the First.Variable or Last.Variable. The values are 1 for True or 0 for False.

 

And why is your "place" variable character if it only has 1 and 0 for values? Kind of odd.

Ksharp
Super User
data have;
  input ID $ (Start End) (:date.) Place ;
  format start end date9.;
cards;
0001 13JAN2015 20JAN2015 0
0001 21JAN2015 31DEC2015 1
0001 01JAN2018 31DEC2018 0
0001 01JAN2019 31DEC2019 1
0002 01JAN2015 31DEC2015 1
0002 01JAN2019 31OCT2019 0
0002 01NOV2019 31DEC2020 0
;

data want;
 set have;
 by id;
 retain found_1 found_0 ;
 if first.id then call missing(found_1,found_0);
 first_1=0;first_0=0;
 if Place=1 and not found_1 then do;first_1=1;found_1=1;end;
 if Place=0 and not found_0 then do;first_0=1;found_0=1;end;
 drop found_:;
run;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 97 views
  • 0 likes
  • 3 in conversation