DATA Step, Macro, Functions and more

How do I write a SAS program ?

Reply
Regular Contributor
Posts: 152

How do I write a SAS program ?

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

Super User
Posts: 6,946

Re: How do I write a SAS program ?

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 336

Re: How do I write a SAS program ?

I would split into weekly and monthly too. Weekly version:

Data A;
  Input Report $1. DateSmiley Very HappyDMMYY10. 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;

Ask a Question
Discussion stats
  • 2 replies
  • 256 views
  • 0 likes
  • 3 in conversation