A New Date Field Required

[Rahul_SAS]

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

[Kurt_Bremser]

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]

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.

[novinosrin · Accepted solution]

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]

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]

Thank you novinosrin for your help.