BookmarkSubscribeRSS Feed
reddy19
Calcite | Level 5

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.

6 REPLIES 6
SASJedi
SAS Super FREQ

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?

Check out my Jedi SAS Tricks for SAS Users
reddy19
Calcite | Level 5

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.

reddy19
Calcite | Level 5

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

art297
Opal | Level 21

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)

reddy19
Calcite | Level 5

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       

art297
Opal | Level 21

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.

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
  • 6 replies
  • 1148 views
  • 3 likes
  • 3 in conversation