topic How do I write a SAS program ? in SAS Programming

How do I write a SAS program ?

gyambqt — Wed, 22 Apr 2015 02:35:30 GMT

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

Re: How do I write a SAS program ?

Kurt_Bremser — Wed, 22 Apr 2015 06:25:47 GMT

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;

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;

prevdate = date;

run;

data want;

set

res_weekly

res_monthly

;

run;

proc sort data=want;

by report date;

run;

Re: How do I write a SAS program ?

user24feb — Wed, 22 Apr 2015 07:58:07 GMT

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;