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

Dear Experts,

 

Required you guidance.

 

Attached 2 files, have and want. I need a new variable reqrddate as mentioned in the want data. 

 

If case id has status as corrected(secondary) and correctionreqrd(primary) both then fetch the date against correctionreqrd otherwise fetch the date against corrected. If case id has status as Complete(primary) then fetch date against complete. The reqrddate should have only one date for each case id.

 

Please refer attached want data for more clarity.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data have;
input caseid :$20.	status :$20.	startdate :mmddyy10.	duedate  :mmddyy10.;
format startdate 	duedate  mmddyy10.;
cards;
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	CorrectionRqrd	6/4/2018	6/15/2018
  1000000013	Corrected	7/6/2018	6/16/2018
  1000000013	CorrectionRqrd	6/6/2018	6/16/2018
  1000000017	Corrected	6/15/2018	6/15/2018
  1000000018	Corrected	6/15/2018	6/15/2018
  1000000019	Complete	6/15/2018	6/15/2018
  1000000020	Corrected	6/15/2018	6/15/2018
  1000000021	Corrected	6/20/2018	6/18/2018
  1000000022	Corrected	6/21/2018	6/18/2018
  1000000024	Corrected	6/22/2018	6/22/2018
  1000000024	CorrectionRqrd	6/21/2018	6/22/2018
  1000000025	Corrected	6/26/2018	6/22/2018
  1000000026	Corrected	6/29/2018	6/22/2018
  1000000032	Corrected	6/22/2018	5/25/2018
  1000000032	CorrectionRqrd	6/22/2018	5/25/2018
  1000000037	CorrectionRqrd	7/4/2018	6/5/2018
  ;

data want;
do until(last.caseid);
set have;
by caseid;
if status='CorrectionRqrd' then reqrddate=startdate;
end;
do until(last.caseid);
set have;
by caseid;
if not reqrddate then reqrddate=startdate;
output;
end;
format reqrddate mmddyy10.;
run;

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

Keep in mind that this is not a Microsoft support forum. If you have questions concerning SAS, post SAS datasets in a usable way (data steps), so we have something to test code against.

Rahul_SAS
Quartz | Level 8

 

novinosrin
Tourmaline | Level 20
data have;
input caseid :$20.	status :$20.	startdate :mmddyy10.	duedate  :mmddyy10.;
format startdate 	duedate  mmddyy10.;
cards;
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	CorrectionRqrd	6/4/2018	6/15/2018
  1000000013	Corrected	7/6/2018	6/16/2018
  1000000013	CorrectionRqrd	6/6/2018	6/16/2018
  1000000017	Corrected	6/15/2018	6/15/2018
  1000000018	Corrected	6/15/2018	6/15/2018
  1000000019	Complete	6/15/2018	6/15/2018
  1000000020	Corrected	6/15/2018	6/15/2018
  1000000021	Corrected	6/20/2018	6/18/2018
  1000000022	Corrected	6/21/2018	6/18/2018
  1000000024	Corrected	6/22/2018	6/22/2018
  1000000024	CorrectionRqrd	6/21/2018	6/22/2018
  1000000025	Corrected	6/26/2018	6/22/2018
  1000000026	Corrected	6/29/2018	6/22/2018
  1000000032	Corrected	6/22/2018	5/25/2018
  1000000032	CorrectionRqrd	6/22/2018	5/25/2018
  1000000037	CorrectionRqrd	7/4/2018	6/5/2018
  ;

data want;
do until(last.caseid);
set have;
by caseid;
if status='CorrectionRqrd' then reqrddate=startdate;
end;
do until(last.caseid);
set have;
by caseid;
if not reqrddate then reqrddate=startdate;
output;
end;
format reqrddate mmddyy10.;
run;
novinosrin
Tourmaline | Level 20
data have;
input caseid :$20.	status :$20.	startdate :mmddyy10.	duedate  :mmddyy10.;
format startdate 	duedate  mmddyy10.;
cards;
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	Corrected	6/5/2018	6/15/2018
  1000000006	CorrectionRqrd	6/4/2018	6/15/2018
  1000000013	Corrected	7/6/2018	6/16/2018
  1000000013	CorrectionRqrd	6/6/2018	6/16/2018
  1000000017	Corrected	6/15/2018	6/15/2018
  1000000018	Corrected	6/15/2018	6/15/2018
  1000000019	Complete	6/15/2018	6/15/2018
  1000000020	Corrected	6/15/2018	6/15/2018
  1000000021	Corrected	6/20/2018	6/18/2018
  1000000022	Corrected	6/21/2018	6/18/2018
  1000000024	Corrected	6/22/2018	6/22/2018
  1000000024	CorrectionRqrd	6/21/2018	6/22/2018
  1000000025	Corrected	6/26/2018	6/22/2018
  1000000026	Corrected	6/29/2018	6/22/2018
  1000000032	Corrected	6/22/2018	5/25/2018
  1000000032	CorrectionRqrd	6/22/2018	5/25/2018
  1000000037	CorrectionRqrd	7/4/2018	6/5/2018
  ;
proc sql;
create table want as 
select *,ifn(max((status='CorrectionRqrd')*startdate)>0,max((status='CorrectionRqrd')*startdate),startdate) as reqrddate format=mmddyy10. 
from have
group by caseid
order by caseid;
quit;
Rahul_SAS
Quartz | Level 8

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
  • 5 replies
  • 1225 views
  • 1 like
  • 3 in conversation