topic Re: A New Date Field Required in SAS Programming

A New Date Field Required

Rahul_SAS — Sat, 07 Jul 2018 15:14:41 GMT

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

Re: A New Date Field Required

Kurt_Bremser — Sat, 07 Jul 2018 16:12:16 GMT

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.

Re: A New Date Field Required

novinosrin — Sat, 07 Jul 2018 16:13:52 GMT

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;

Re: A New Date Field Required

novinosrin — Sat, 07 Jul 2018 16:33:25 GMT

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;

Re: A New Date Field Required

Rahul_SAS — Sat, 07 Jul 2018 16:37:43 GMT

Sure KurtBresmer, will keep that in mind.

i am a new sas user os got stuck and was not getting any solution. tested many ways.

Re: A New Date Field Required

Rahul_SAS — Sat, 07 Jul 2018 16:38:55 GMT

Thank you novinosrin for your help.