Hello experts,
how do I write a SAS code to identify any missing "Report" using the following data.
The date interval for the weekly report is 7 days. for example, if the first report is on 01/01/2015 and second report is on 15/01/2015 then there is a report missing on 08/01/2015. the final output should only include the missing report's (report name, date, frequency)
For monthly report, however the date interval for the monthly report is not exactly 30 days but for example if the first report and last report is on jan 2015 and may2015 then there must be one report every month between Jan2015 and May2015 , if for a particular month between Jan2015 and May2015, there is no report then the report is missing then I want to identify the report name, month and its frequency.
Report Date Frequency
a 01/01/2015 weekly
a 15/01/2015 weekly
a 22/01/2015 weekly
b 03/02/2015 weekly
b 10/02/2015 weekly
b 17/01/2015 weekly
c 10/01/2015 monthly
c 12/02/2015 monthly
c 10/04/2015 monthly
c 12/05/2015 monthly
...
....
many records
For clarity, I split the two checks
proc sort
data=have (where=(frequency='weekly'))
out=int_weekly
;
by report date;
run;
data res_weekly (keep=report intdate frequency rename=(intdate=date));
set int_weekly;
by report;
format intdate prevdate ddmmyy10.;
retain prevdate;
if not first.report then do;
do intdate = prevdate + 7 to date - 1 by 7;
output;
end;
end;
prevdate = date;
run;
proc sort
data=have (where=(frequency='monthly'))
out=int_monthly
;
by report date;
run;
data res_monthly (keep=report intdate frequency rename=(intdate=date));
set int_monthly;
by report;
format intdate prevdate ddmmyy10.;
retain prevdate;
if not first.report then do;
intdate = intnx('month',prevdate,1,'begin');
do while (month(intdate) < month(date));
output;
intdate = intnx('month',intdate,1,'begin');
end;
end;
prevdate = date;
run;
data want;
set
res_weekly
res_monthly
;
run;
proc sort data=want;
by report date;
run;
I would split into weekly and monthly too. Weekly version:
Data A;
Input Report $1. Date:DDMMYY10. Frequency $;
Format Date Date9.;
Datalines;
a 01/01/2015 weekly
a 15/01/2015 weekly
a 22/01/2015 weekly
b 03/02/2015 weekly
b 10/02/2015 weekly
b 17/01/2015 weekly
c 10/01/2015 monthly
c 12/02/2015 monthly
c 10/04/2015 monthly
c 12/05/2015 monthly
;
Proc Sort Data=A;
By Report Date;
Run;
Data B (Keep=Report Date: Missing_Report_Date);
Length Report $1. Date 8;
Length Date_Previous 8; Format Date_Previous Date9.;
Length Missing_Report_Date 8; Format Missing_Report_Date Date9.;
If _N_ eq 1 Then Do;
Declare Hash H (Dataset:'A (Where=(Frequency="weekly"))',Multidata:'y',Ordered:'y');
Declare Hiter HI ('H');
H.Definekey('Report','Date');
H.Definedone();
End;
Set A (Where=(Frequency="weekly"));
rc=HI.First();
Do While (not rc);
Date_Previous=Date;
Report_Prev=Report;
rc=HI.Next();
Delta=IntCK('week',Date_Previous,Date);
If (Report_Prev eq Report) Then Do;
Do i=1 To Delta-1;
Missing_Report_Date=IntNX('week',Date_Previous,i,'same');
Output;
End;
End;
End;
Stop;
Run;
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.