BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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*/

View solution in original post

5 REPLIES 5
ballardw
Super User

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:

twildone
Pyrite | Level 9

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.

ballardw
Super User

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*/

twildone
Pyrite | Level 9

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.

twildone
Pyrite | Level 9

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.

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
  • 5 replies
  • 851 views
  • 3 likes
  • 2 in conversation