I've tried a variety of things. I can get the output to one row per r_id which is what I want but the result is not correct.
If there is no strt_dt for a r_id then we want the earliest creat_dt.
if there is a strt_dt for a r_id we want the earliest strt_dt.
This is what the result should look like
J1178 03/05/2014
J2256 03/22/2014
J2256 03/26/2014
J3363 03/19/2014
J4444 03/24/2014
J5522 03/24/2014
J8434 03/24/2014
data have;
input @1 r_id $5. @7 creat_dttm mmddyy10. @18 strt_dt mmddyy10. ;
format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014
J2256 03/26/2014
J3363 03/18/2014 03/19/2014
J8434 03/19/2014
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014
J8434 03/11/2014
J4444 03/21/2014 03/24/2014
J5522 03/24/2014
J2256 03/26/2014 03/22/2014
J3363 03/10/2014 03/21/2014
J1178 03/24/2014
;
run;
proc sort data=have;
by r_id strt_dt creat_dttm;
run;
data new;
set have;
by r_id strt_dt creat_dttm;
format newdate maybe_dt mmddyy10.;
if first.r_id then do;
retain newdate .;
retain maybe_dt .;
end;
if first.r_id and strt_dt =. then do;
maybe_dt=creat_dttm;
put 'new' newdate;
end;
else if first.r_id and strt_dt !. then newdate=strt_dt;
else if newdate =. then newdate=strt_dt;
if newdate =. then newdate=strt_dt;
if last.r_id and newdate =. then do;
newdate=maybe_dt;
output;
end;
/* else if newdate !. then do;*/
/* newdate=maybe_dt;*/
/* output;*/
/* end;*/
run;
Any help is greatly appreciated. Thanks.
Try next code:
data have;
input @1 r_id $5. @7 creat_dttm mmddyy10. @18 strt_dt mmddyy10. ;
format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014
J2256 03/26/2014
J3363 03/18/2014 03/19/2014
J8434 03/19/2014
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014
J8434 03/11/2014
J4444 03/21/2014 03/24/2014
J5522 03/24/2014
J2256 03/26/2014 03/22/2014
J3363 03/10/2014 03/21/2014
J1178 03/24/2014
;
run;
proc sort data=have;
by r_id creat_dttm strt_dt;
run;
data new;
set have;
by r_id;
retain newdate flag;
format newdate mmddyy10.;
if first.r_id then do;
newdate = creat_dttm;
flag = 0;
end;
if not missing(strt_dt) and flag=0
then do; newdate = strt_dt; flag=1; end;
if last.r_id then output;
keep r_id newdate;
run;
Try next code:
data have;
input @1 r_id $5. @7 creat_dttm mmddyy10. @18 strt_dt mmddyy10. ;
format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014
J2256 03/26/2014
J3363 03/18/2014 03/19/2014
J8434 03/19/2014
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014
J8434 03/11/2014
J4444 03/21/2014 03/24/2014
J5522 03/24/2014
J2256 03/26/2014 03/22/2014
J3363 03/10/2014 03/21/2014
J1178 03/24/2014
;
run;
proc sort data=have;
by r_id creat_dttm strt_dt;
run;
data new;
set have;
by r_id;
retain newdate flag;
format newdate mmddyy10.;
if first.r_id then do;
newdate = creat_dttm;
flag = 0;
end;
if not missing(strt_dt) and flag=0
then do; newdate = strt_dt; flag=1; end;
if last.r_id then output;
keep r_id newdate;
run;
Excellent. That's it. Thank you so much for the help and fast response.
@DanD999 wrote:
...
If there is no strt_dt for a r_id then we want the earliest creat_dt.
if there is a strt_dt for a r_id we want the earliest strt_dt.
If should be pretty straight forward to translate that into SQL.
data have;
input r_id :$5. creat_dttm :mmddyy. strt_dt :mmddyy. ;
format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014 .
J2256 03/26/2014 .
J3363 03/18/2014 03/19/2014
J8434 03/19/2014 .
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014 .
J8434 03/11/2014 .
J4444 03/21/2014 03/24/2014
J5522 03/24/2014 .
J2256 03/26/2014 03/22/2014
J3363 03/10/2014 03/21/2014
J1178 03/24/2014 .
;
proc sql noprint;
create table want as
select r_id
, coalesce(min(strt_dt),min(creat_dttm)) as newdate format=mmddyy10.
from have
group by r_id
;
quit;
Results:
Obs r_id newdate 1 J1178 03/05/2014 2 J2256 03/22/2014 3 J3363 03/19/2014 4 J4444 03/24/2014 5 J5522 03/24/2014 6 J8434 03/24/2014
@Tom wrote:
@DanD999 wrote:
...
If there is no strt_dt for a r_id then we want the earliest creat_dt.
if there is a strt_dt for a r_id we want the earliest strt_dt.
If should be pretty straight forward to translate that into SQL.
data have; input r_id :$5. creat_dttm :mmddyy. strt_dt :mmddyy. ; format creat_dttm strt_dt mmddyy10.; cards; J3363 03/17/2014 . J2256 03/26/2014 . J3363 03/18/2014 03/19/2014 J8434 03/19/2014 . J2256 03/24/2014 04/29/2014 J8434 03/20/2014 03/24/2014 J1178 03/05/2014 . J8434 03/11/2014 . J4444 03/21/2014 03/24/2014 J5522 03/24/2014 . J2256 03/26/2014 03/22/2014 J3363 03/10/2014 03/21/2014 J1178 03/24/2014 . ; proc sql noprint; create table want as select r_id , coalesce(min(strt_dt),min(creat_dttm)) as newdate format=mmddyy10. from have group by r_id ; quit;
Results:
Obs r_id newdate 1 J1178 03/05/2014 2 J2256 03/22/2014 3 J3363 03/19/2014 4 J4444 03/24/2014 5 J5522 03/24/2014 6 J8434 03/24/2014
That does also work. Thanks.
Hi:
I'm confused by your data and what you say you want:
Why do you show only 1 row per ID in your desired output but show 2 rows for J2256? It is not clear to me whether you expect to see 13 rows in the final output, 6 rows in the final output or 7 rows in the final output. Can you clarify?
Cynthia
@Cynthia_sas wrote:
Hi:
I'm confused by your data and what you say you want:
Why do you show only 1 row per ID in your desired output but show 2 rows for J2256? It is not clear to me whether you expect to see 13 rows in the final output, 6 rows in the final output or 7 rows in the final output. Can you clarify?
Cynthia
I did say in the initial post "I can get the output to one row per r_id which is what I want"
Thanks for the reply.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.