BookmarkSubscribeRSS Feed
gyambqt
Obsidian | Level 7

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

2 REPLIES 2
Kurt_Bremser
Super User

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;

user24feb
Barite | Level 11

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;

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
  • 2 replies
  • 788 views
  • 0 likes
  • 3 in conversation