Dynamically create tables based on string values in macro variable

[PegaZeus]

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.

[ballardw]

---

@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.

[PegaZeus]

@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

[Kurt_Bremser]

Remove the

,%str()

from the %SCAN call.

[ballardw · Accepted solution]

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.