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.
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?
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.
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
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)
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.