Hi..I am trying to genarate separate pdf files based on the Client_ID. The code does generate the results for the first Client_ID in the list but does not loop to continue with the remaining Client_ID's on the list. Am I missing something.....Any help or suggestions would greatly appreciated....Thanks.
%MACRO DO_CLIENTS;
%DO i=1 %TO %SYSFUNC(COUNTW(&WHENS));
%LET NEXT_CLIENT = %SCAN(&WHENS, &i);
%LET NEXT_NOBS = %SCAN(&NEXT_NOBS, &i);
%LET NEXT_FILENAME = %SCAN(&FILENAMELIST, &i);
%LET NEXT_NAME = %SCAN(&NAMELIST, &i);
%LET NEXT_FROMDATE3 = %SCAN(&FROMDATE2LIST, &i);
%LET NEXT_TODATE3 = %SCAN(&TODATE2LIST, &i);
%LET RUNDATE = &RUNDATE;
DATA SALES;
SET SUMMARYFINAL;
IF CLIENT_ID = &NEXT_CLIENT;
RUN;
ODS _ALL_ CLOSE;
ODS LISTING CLOSE;
ODS NORESULTS;
OPTIONS ORIENTATION=PORTRAIT CENTER NODATE NONUMBER NOBYLINE
TOPMARGIN=".5IN"
BOTTOMMARGIN=".5IN"
LEFTMARGIN=".5IN"
RIGHTMARGIN=".5IN";
%LET RSTR = %STR( );
%LET TSTR = %SYSFUNC(REPEAT(&RSTR,50));
ODS ESCAPECHAR='^';
ODS PDF FILE="%sysfunc(pathname(project))\&NEXT_FILENAME Report (&rundate).PDF" STYLE=JOURNAL NOTOC BOOKMARKGEN=NO BOOKMARKLIST=NONE;
TITLE1 JUSTIFY=CENTER BOLD HEIGHT=12PT FONT="Arial" "Client Purchases History";
TITLE2 " ";
TITLE3 JUSTIFY=LEFT HEIGHT=9PT FONT=Arial "Client Name: &NEXT_NAME" JUSTIFY=RIGHT "Report Date From: &NEXT_FROMDATE3";
TITLE4 JUSTIFY=LEFT HEIGHT=9PT FONT=Arial "CLIENT_ID #: &NEXT_CLIENT" JUSTIFY=RIGHT "Report Date End: &NEXT_TODATE3";
TITLE5 " ";
TITLE6 "^{style[bordertopwidth=2px bordertopcolor=black] &tstr}";
DATA _NULL_;
%IF CLIENT_ID = &NEXT_CLIENT AND &NEXT_NOBS = 0 %THEN %DO;
FILE PRINT FLOWOVER N=PAGESIZE;
%PUT "A query for the Purchases History for &NEXT_NAME with CLIENT_ID: &NEXT_CLIENT for the period &next_fromdate3 to &next_todate3 has revealed that no purchases has been made for this client during this time period.";
%END;
STOP;
%IF CLIENT_ID = &NEXT_PATIENT AND &NEXT_NOBS > 0 %THEN;
PROC REPORT DATA=SALES NOWD HEADLINE HEADSKIP MISSING ps=43 ls=108
STYLE(REPORT)={frame=box}
STYLE(HEADER)={font_weight=bold background=lightgrey font_face=Arial
font_size=8pt borderwidth=1px bordercolor=black just=CENTER}
STYLE(COLUMN)={background=white font_face=Arial font_size=7pt borderwidth=1px
bordercolor=black just=CENTER}
STYLE(LINES)={JUST=C FONT_WEIGHT=BOLD FONT_SIZE=11PT};
COLUMN (SALE_DATE CLIENT_SURNAME CLIENT_GIVEN PRODUCT_N PROD_G QTY AMOUNT);
DEFINE SALE_DATE / DISPLAY 'Sale Date';
DEFINE PRESCRIBER_SURNAME / DISPLAY 'Prescriber Surname';
DEFINE PRESCRIBER_GIVEN / DISPLAY 'Prescriber First Name';
DEFINE PRODUCT_N / DISPLAY 'Product Number' STYLE(COLUMN)={TAGATTR='00000000'};
DEFINE PROD_G / DISPLAY 'Product Name';
DEFINE QTY / DISPLAY 'Quantity';
DEFINE AMOUNT / DISPLAY 'Total Amount';
RUN;
QUIT;
%LET i = %EVAL(&i + 1);
%END;
%MEND;
%DO_CLIENTS;
ODS PDF CLOSE;
ODS LISTING;
ODS RESULTS;
Run your code with option MPRINT on to get a better idea where the macro errors are occurring.
Issues:
DATA _NULL_;
%IF CLIENT_ID = &NEXT_CLIENT AND &NEXT_NOBS = 0 %THEN %DO;
FILE PRINT FLOWOVER N=PAGESIZE;
%PUT "A query for the Purchases History for &NEXT_NAME with CLIENT_ID: &NEXT_CLIENT for the period &next_fromdate3 to &next_todate3 has revealed that no purchases has been made for this client during this time period.";
%END;
STOP;
Since that data step does not have a SET statement there is no value for the variable Client_ID AND you wouldn't want to use a dataset variable in a MACRO %if. So the macro compiler is comparing the literal text CLIENT_ID to the current value of &Next_client. Bet that you never get a true result. I would expect there to be a macro variable Client_id somewhere but you don't define one. the IF CLIENT_ID = &NEXT_CLIENT; in the Data Sales works because it is using a data step IF with a data step variable and comparing to a value from the macro variable.
Since you are looking for a data step to write to FILE PRINT you do not want %PUT, use PUT.
You would be better to use RUN than STOP to end the data step.
Also before the Proc report:
%IF CLIENT_ID = &NEXT_PATIENT AND &NEXT_NOBS > 0%THEN;
Same issue of not using a Macro variable. Also likely should be a %then %do;
and add another %end;
I think that you may be looking for a structure like:
%IF &CLIENT_ID = &NEXT_CLIENT AND &NEXT_NOBS = 0 %THEN %DO;
<the data _null_ to print>
%end;
%else %do;
<the proc report>
%end; /*ends the IF stuff*/
You likely don't want:
%LET i = %EVAL(&i + 1);
The start of the loop
%DO i=1%TO%SYSFUNC(COUNTW(&WHENS));
will automatically increment the loop counter when the matching %end is executed. By incrementing it yourself you actually skip the second value in the lists. If you only have two items then it will appear to be missing:
I deleted that statement and I am getting the following error message:
ERROR: Macro function %SCAN has too many arguments. The excess arguments will be ignored.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required.
ERROR: Argument 2 to macro function %SCAN is not a number.
Run your code with option MPRINT on to get a better idea where the macro errors are occurring.
Issues:
DATA _NULL_;
%IF CLIENT_ID = &NEXT_CLIENT AND &NEXT_NOBS = 0 %THEN %DO;
FILE PRINT FLOWOVER N=PAGESIZE;
%PUT "A query for the Purchases History for &NEXT_NAME with CLIENT_ID: &NEXT_CLIENT for the period &next_fromdate3 to &next_todate3 has revealed that no purchases has been made for this client during this time period.";
%END;
STOP;
Since that data step does not have a SET statement there is no value for the variable Client_ID AND you wouldn't want to use a dataset variable in a MACRO %if. So the macro compiler is comparing the literal text CLIENT_ID to the current value of &Next_client. Bet that you never get a true result. I would expect there to be a macro variable Client_id somewhere but you don't define one. the IF CLIENT_ID = &NEXT_CLIENT; in the Data Sales works because it is using a data step IF with a data step variable and comparing to a value from the macro variable.
Since you are looking for a data step to write to FILE PRINT you do not want %PUT, use PUT.
You would be better to use RUN than STOP to end the data step.
Also before the Proc report:
%IF CLIENT_ID = &NEXT_PATIENT AND &NEXT_NOBS > 0%THEN;
Same issue of not using a Macro variable. Also likely should be a %then %do;
and add another %end;
I think that you may be looking for a structure like:
%IF &CLIENT_ID = &NEXT_CLIENT AND &NEXT_NOBS = 0 %THEN %DO;
<the data _null_ to print>
%end;
%else %do;
<the proc report>
%end; /*ends the IF stuff*/
Hi Ballardw, I was able to isolate where the problem is. It has to with these 2 statements:
%LET NEXT_FROMDATE3 = %SCAN(&FROMDATE2LIST, &i);
%LET NEXT_TODATE3 = %SCAN(&TODATE2LIST, &i);
The dates in both of these lists are in worddate format (January 10, 2015, for example). I guess I need to specify how many words are in the string and the delimiters.
Hi Ballardw.....Thanks for your suggestions and help. I made the changes as you had suggested. It works and loops through as I was hoping it would. I might play around and try and resolve the issue with the worddate but otherwise I can live with it as being in the yymmdd8. format. Thanks once again.
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.
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.