Hello,
I'm attempting to create a number of tables with a macro as such, however it doesn't produce any results:
%macro my_loop(table_nme);
proc sql;
select distinct auth_cd into :codes separated by ' ' from pth.code_names;
quit;
%do i=1 %to %sysfunc(countw(&codes));
%let my_codes=%scan(&codes,&i,%str());
proc sql;
create table &table_nme as
select distinct
auth_id,
prd_rate
from aop.tracked_codes
where auth_id = &my_codes;
quit;
%end;
%mend;
%my_loop(first_table);
So basically the goal is to create one table for each value in the &my_codes list, so I am trying to loop through each of the values in the list and create tables for each.
The %scan creating your my_client macro variable is having problems because you have told it to use an zero length string value as the delimiter. The space is a native delimiter for %scan and does not need to be provided. But you did not use a space the %str() has no character inside the quotes, leading to unexpected results. I am not sure why your result has all of the values in the result instead of an empty string, perhaps an unusual scope issue.
Check the results running this code:
%let clients= BCBSA BCBSAL BCBSFL BCBSIL BCBSKS BCBSMN; %let my_client = %scan(&clients.,2); /*<= use default delimiters for the %scan function */ %put second my_client is &my_client.; %let my_client2 = %scan(&clients.,2,%str( )); %put second my_client2 is &my_client2.; %let my_client3 = %scan(&clients.,2,%str()); %put second my_client3 is &my_client3.;
From the documentation for %scan
If you use the %SCAN function with only two arguments, then the default delimiters depend on whether your computer uses ASCII or EBCDIC characters.
If your computer uses ASCII characters, then the default delimiters are as follows:blank ! $ % & ( ) * + , - . / ; < ^¦In ASCII environments that do not contain the ^ character, the %SCAN function uses the ~ character instead. If your computer uses EBCDIC characters, then the default delimiters are as follows:blank ! $ % & ( ) * + , - . / ; < ¬ | ¢¦
So there was no need to attempt to provide a specific delimiter in this case.
@PegaZeus wrote:
Hello,
I'm attempting to create a number of tables with a macro as such, however it doesn't produce any results:
%macro my_loop(table_nme); proc sql; select distinct auth_cd into :codes separated by ' ' from pth.code_names; quit; %do i=1 %to %sysfunc(countw(&codes)); %let my_codes=%scan(&codes,&i,%str()); proc sql; create table &table_nme as select distinct auth_id, prd_rate from aop.tracked_codes where auth_id = &my_codes; quit; %end; %mend; %my_loop(first_table);
So basically the goal is to create one table for each value in the &my_codes list, so I am trying to loop through each of the values in the list and create tables for each.
Since your example uses the same name value in the create table clause then you overwrite the table each time you loop.
You don't provide any example of what the MY_codes values may be so we can't tell if they could be used for table names or not due to length or invalid characters.
create table &table_nme.&i as
would place a suffix of the loop counter into the name.
You likely also need to have
where auth_id = "&my_codes";
so the value of auth_id is compared to a character value.
When your code creates errors, which I strongly suspect yours did, then copy from the log the submitted code and any messages, then paste into a code box on the forum opened with the </> icon.
When debugging macros you will want to learn about the options involved for creating a more complete log.
Start with placing:
Options mprint;
before the macro submission line..
Then turn off by placing after
Options nomprint;
Other useful options are SYMBOLGEN, if it have issues with combining things to make variable names and such, or MLOGIC if the concern seems to be the result of comparisons in the macro language.
Hint: Did you have a Proc SQL code that worked for a single value before attempting this macro? If not, that is always the first step. Get non-macro code working, Then make changes to incorporate the macro elements.
@ballardw Thank you for these details. Okay so, this is the actual code I'm using (I had mocked up that example to try and make it simpler) as well as the log. What I'm running into I think is on that where filter as well as the create table, it puts in the whole list of strings.
Code:
%macro client_loop();
proc sql;
select distinct clnt_cd into :clients separated by ' ' from com.triessent_fee_schedule;
quit;
%do i=1 %to %sysfunc(countw(&clients));
%let my_client=%scan(&clients,&i,%str());
proc sql;
create table &my_client as
select distinct
t1.ndc_num as ndc,
t1.exclrate as exclusive_rate format=percent9.4,
lmtddistr as ldd_flag,
(0) as quality_network_rate,
(0) as non_exclusive_rate
from com.specialty_fee_schedule t1
where t1.clnt_cd = "&my_client" and t1.exclrate > 0 and fee_schedule_dml_based_flag="YES";
quit;
%end;
%mend;
options mprint;
%client_loop();
You'll notice that I put &my_client in the create table. The values in the client list are things like: BCBSMN, BCBSIL, BCBSFL...they are not too long for a table name and there are 22 of them.
Here is what I'm running into in the log from running the above code:
NOTE: Implicit SQL: select distinct TXT_1.Clnt_CD from TRIESSENT_FEE_SCHEDULE TXT_1 WARNING: No output destinations active. MPRINT(CLIENT_LOOP): quit; NOTE: PROCEDURE SQL used (Total process time): 2 The SAS System 11:15 Thursday, October 8, 2020 real time 4.08 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 5261.00k OS Memory 20900.00k Timestamp 10/08/2020 11:16:06 AM Step Count 2 Switch Count 58 Page Faults 1 Page Reclaims 204 Page Swaps 0 Voluntary Context Switches 201 Involuntary Context Switches 2 Block Input Operations 0 Block Output Operations 0 MPRINT(CLIENT_LOOP): proc sql; NOTE: Line generated by the macro variable "MY_CLIENT". 37 BCBSA BCBSAL BCBSFL BCBSIL BCBSKS BCBSMN BCBSMT BCBSNC BCBSND BCBSNE BCBSNJ BCBSNM BCBSOK BCBSRI BCBSTX BCBSTX09 BCBSWY ______ 22 76 37 ! BOEING CBC CHP PRIME REGENCE MPRINT(CLIENT_LOOP): create table BCBSA BCBSAL BCBSFL BCBSIL BCBSKS BCBSMN BCBSMT BCBSNC BCBSND BCBSNE BCBSNJ BCBSNM BCBSOK BCBSRI BCBSTX BCBSTX09 BCBSWY BOEING CBC CHP PRIME REGENCE as select distinct t1.ndc_num as ndc, t1.exclrate as exclusive_rate format=percent9.4, lmtddistr as ldd_flag, (0) as quality_network_rate, (0) as non_exclusive_rate from com.specialty_fee_schedule t1 where t1.clnt_cd = "BCBSA BCBSAL BCBSFL BCBSIL BCBSKS BCBSMN BCBSMT BCBSNC BCBSND BCBSNE BCBSNJ BCBSNM BCBSOK BCBSRI BCBSTX BCBSTX09 BCBSWY BOEING CBC CHP PRIME REGENCE" and t1.exclrate > 0 and fee_schedule_dml_based_flag="YES"; ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. MPRINT(CLIENT_LOOP): quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 46.71k OS Memory 16288.00k Timestamp 10/08/2020 11:16:07 AM Step Count 3 Switch Count 52 Page Faults 1 Page Reclaims 12 Page Swaps 0 Voluntary Context Switches 154 Involuntary Context Switches 2 Block Input Operations 0 Block Output Operations 0
Remove the
,%str()
from the %SCAN call.
The %scan creating your my_client macro variable is having problems because you have told it to use an zero length string value as the delimiter. The space is a native delimiter for %scan and does not need to be provided. But you did not use a space the %str() has no character inside the quotes, leading to unexpected results. I am not sure why your result has all of the values in the result instead of an empty string, perhaps an unusual scope issue.
Check the results running this code:
%let clients= BCBSA BCBSAL BCBSFL BCBSIL BCBSKS BCBSMN; %let my_client = %scan(&clients.,2); /*<= use default delimiters for the %scan function */ %put second my_client is &my_client.; %let my_client2 = %scan(&clients.,2,%str( )); %put second my_client2 is &my_client2.; %let my_client3 = %scan(&clients.,2,%str()); %put second my_client3 is &my_client3.;
From the documentation for %scan
If you use the %SCAN function with only two arguments, then the default delimiters depend on whether your computer uses ASCII or EBCDIC characters.
If your computer uses ASCII characters, then the default delimiters are as follows:blank ! $ % & ( ) * + , - . / ; < ^¦In ASCII environments that do not contain the ^ character, the %SCAN function uses the ~ character instead. If your computer uses EBCDIC characters, then the default delimiters are as follows:blank ! $ % & ( ) * + , - . / ; < ¬ | ¢¦
So there was no need to attempt to provide a specific delimiter in this case.
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.