DATA Step, Macro, Functions and more

A New Date Field Required

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 79
Accepted Solution

A New Date Field Required

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


Accepted Solutions
Solution
a week ago
PROC Star
Posts: 1,772

Re: A New Date Field Required

Posted in reply to Rahul_SAS
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


All Replies
Super User
Posts: 10,215

Re: A New Date Field Required

Posted in reply to Rahul_SAS

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 79

Re: A New Date Field Required

Posted in reply to KurtBremser

 

Solution
a week ago
PROC Star
Posts: 1,772

Re: A New Date Field Required

Posted in reply to Rahul_SAS
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;
PROC Star
Posts: 1,772

Re: A New Date Field Required

Posted in reply to novinosrin
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;
Frequent Contributor
Posts: 79

Re: A New Date Field Required

Posted in reply to novinosrin

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 139 views
  • 1 like
  • 3 in conversation