DATA Step, Macro, Functions and more

generating error report for each quater between dates.

Reply
Contributor
Posts: 20

generating error report for each quater between dates.

I have a dataset with dates from 1jan2010-26aug2011.
Actual dataset is having million records.I have two
date prompts date1 and date2.i'm giving date1 value as 1jan2010.
and date2 value as 26aug2011.So it is pulling data between dates.

Pharmacy CASE_ID type          product  Date
SDI ACS   1 Approval 0 
SDI ACS   1 Referral 0  3/9/2011
SDI ACS   1 Ship  50242006301 3/9/2011
SDI ACS   1 Ship  50242006301 4/9/2011
SDI ACS   10 Approval 0 
SDI ACS   10 Ship  50242006201 11/2/2010
SDI ACS   10 Ship  50242006201 11/28/2010
SDI ACS   10 Ship  50242006201 12/28/2010


From the above pull....i have to calculate error report for
each quater based on date1 and date2.

I have to put all quater reports in same file but quater start date and end date column
should specify for which quater we r creating report.

I have already created error report for period date1-date2.But i don't know how
to calculate based on the quater.

Can somebody help me.

Thanks,

reddy.

SAS Employee
Posts: 104

generating error report for each quater between dates.

If you provide a sample of the output you are trying to produce, I could probably provide more specific advice.  But perhaps applying the QTR. or YYQ. formats to your date could help?

Contributor
Posts: 20

generating error report for each quater between dates.

Jedi,

       Here is the sample output

       start date= 1 jan 2010

       end date = 1 jan 2011

I have to produce report for each quater....i mean 1jan2010-31mar2010.....q1

1apr2010-30june2010.........q2

1-july2010-30sep 2010.......q3.

error report:

pharmacy   startdate    enddate          no. of missing dates   no.of missing types.

acs            1jan2010   31mar2010       2                              2

acs            1apr2010   30june2010       5                             5

Like above i have to get error count for each quater between start date and end date.I know how to count those errors.

but i don't know how to run that logic for each quater between those dates.

Thanks for u r help.

reddy.

Contributor
Posts: 20

generating error report for each quater between dates.

How should i make it repeat for each quater.

DATA _NULL_;
CALL SYMPUT('qtr_start', "'"|| TRIM(LEFT(PUT(
INTNX('MONTH',input("&Start_Date",date9.),0,'BEG'),
DATE9.))) ||"'d") ;
CALL SYMPUT('qtr_end', "'"|| TRIM(LEFT(PUT(
INTNX('MONTH',input("&Start_Date",date9.),2,'END'),
DATE9.))) ||"'d") ;
RUN ;
%PUT End of Quarter= &start ;
%PUT End of Quarter= &end ;

PROC SQL ;
SELECT ...
FROM ... …
WHERE (filedate BETWEEN &start
AND &end ;
QUIT ;

regards,

reddy

PROC Star
Posts: 7,474

generating error report for each quater between dates.

One possibility is to simply wrap your code in a macro.  For the following macro, you would call it by entering the date of the start of the first quarter you were interested in and the number of quarters:

%macro do_qtr(start_qtr1,quarters);

  %do i=1 %to &quarters.;

    %let start=%sysfunc(intnx(month ,

         "&start_qtr1."d , %eval(&i*3-3),s )) ;

    %let end=%sysfunc(intnx(qtr ,

         "&start_qtr1."d , %eval(&i-1),e )) ;

    %PUT Start of Quarter= &start ;

    %PUT End of Quarter= &end ;

    PROC SQL ;

      SELECT ...

        FROM ... …

          WHERE (filedate BETWEEN &start

           AND &end

      ;

    QUIT ;

  %end;

%mend do_qtr;

%do_qtr(01jan2009,7)

Contributor
Posts: 20

generating error report for each quater between dates.

Hi Art297,

           Your answer helped me a lot.....But i have few questions.

           

           Is it possible to pass the macro parameters through prompt?..

           If so,can you suggest me how can i do that one.

           I'm keeping above program in the EG workflow....so user

           won't have any idea how to run macro.

  Thanks,

  reddy       

PROC Star
Posts: 7,474

generating error report for each quater between dates.

Reddy,

I've never used EG, thus don't know the answer to your question.  I presume you could use the window statement in EG.  For example, the following code creates a window in which a user can enter a value that can be used later in the code:

DATA _NULL_;

WINDOW DSN rows=8 columns=80

irow=1 icolumn=2 color=black

#2 @3 'Enter 1 or 2 level data set name: '

color=gray dsn $41. required=yes

attr=underline color=yellow;

DISPLAY DSN blank;

run;

In the above case, a user can enter a value that will go into the variable DSN.

Possibly that is what you are looking for.  Conversely, you don't have to pass values into a macro.  They can simply be established with %let statements before you run the macro.

Ask a Question
Discussion stats
  • 6 replies
  • 242 views
  • 3 likes
  • 3 in conversation