Using a macro variable with into: in PROC SQL

[irvinery]

Hi all,

I am running a query in a database that includes data for different countries. I am looking to place product IDs into macro variables, which is a simple enough process. However, because the product IDs are different by country, they need to be assigned to separate macro variables. There are two separate product lookup tables, one for each country, France (FR) and Spain (ES), where the table contains a product_ID and a corresponding label (basically the product name).

This is the macro that I wrote, which is not working.

%macro insulin_IDs(country);
proc sql noprint;
select "'"|| compress(product_ID)||"'"
into :&country._insulin_products separated by ", " 
from thin.dwh_&country._products
where lower(label) contains "insulin";
quit;
%mend;

%insulin_IDs (FR);
%insulin_IDs (ES);

The result should output two macro vars, FR_insulin_products and ES_insulin_products. Based on the sample data included at the end, the correct output would be:

('FR 1', 'FR 2', 'FR 3', 'FR 4', 'FR 5')

('ES 1', 'ES 2', 'ES 3', 'ES 4', 'ES 5')

However, it does not work due to the "into :&country._insulin_products".

When I run the following code, it works as expected with no issue and correctly outputs the two macro variables:

proc sql noprint;
select "'"|| compress(product_ID)||"'"
into :FR_insulin_products separated by ", " 
from thin.dwh_FR_products
where lower(label) contains "insulin";
quit;

proc sql noprint;
select "'"|| compress(product_ID)||"'"
into :ES_insulin_products separated by ", " 
from thin.dwh_ES_products
where lower(label) contains "insulin";
quit;

Sample data:

data FR_products;
   input product_id $4. label $20.;
   datalines;
FR 1 FR Insulin Product 1
FR 2 FR Insulin Product 2
FR 3 FR Insulin Product 3
FR 4 FR Insulin Product 4
FR 5 FR Insulin Product 5
;

data ES_products;
   input product_id $4. label $20.;
   datalines;
ES 1 ES Insulin Product 1
ES 2 ES Insulin Product 2
ES 3 ES Insulin Product 3
ES 4 ES Insulin Product 4
ES 5 ES Insulin Product 5
;

Is this just something that isn't possible with PROC SQL? Thanks in advance for any advice or solutions.

[Tom]

Take a step back and explain WHY you feel you need those macro variables.

What code to you intend to generate with them?  What is the purpose of that code?  Could you do the same thing in another way that does not involve macro variables?

[Tom]

I don't understand what the issue is you are having.  (Personally I try to avoid commas in macro variables as it makes them hard to use.)

data FR_products;
   input product_id $4. label $25.;
   datalines;
FR 1 FR Insulin Product 1
FR 2 FR Insulin Product 2
FR 3 FR Insulin Product 3
FR 4 FR Insulin Product 4
FR 5 FR Insulin Product 5
;

proc sql noprint;
select quote(trim(product_id),"'")
     , quote(trim(label),"'")
  into :code_list separated by ' '
     , :label_list separated by ' '
  from fr_products
;
quit;

Results:

3381
3382  %put &=sqlobs;
SQLOBS=5
3383  %put &=code_list ;
CODE_LIST='FR 1' 'FR 2' 'FR 3' 'FR 4' 'FR 5'
3384  %put &=label_list ;
LABEL_LIST='FR Insulin Product 1' 'FR Insulin Product 2' 'FR Insulin Product 3' 'FR Insulin Product 4' 'FR Insulin Product 5'

[Reeza · Accepted solution]

Macro variable scope - the macro variable disappears when the macro ends otherwise.

Add the line in red to your code.

FYI-IMO a format would be easier to deploy using PUTC to dynamically generate the name and keep the formats defined.

%macro insulin_IDs(country);

%global &country._insulin_products;

proc sql noprint;
select "'"|| compress(product_ID)||"'"
into :&country._insulin_products separated by ", " 
from 

&country._products
where lower(label) contains "insulin";
quit;
%mend;

data FR_products;
   input product_id $4. label $20.;
   datalines;
FR 1 FR Insulin Product 1
FR 2 FR Insulin Product 2
FR 3 FR Insulin Product 3
FR 4 FR Insulin Product 4
FR 5 FR Insulin Product 5
;

data ES_products;
   input product_id $4. label $20.;
   datalines;
ES 1 ES Insulin Product 1
ES 2 ES Insulin Product 2
ES 3 ES Insulin Product 3
ES 4 ES Insulin Product 4
ES 5 ES Insulin Product 5
;



%insulin_IDs (FR);
%insulin_IDs (ES);

%put &fr_insulin_products;
%put &es_insulin_products;

[irvinery]

Thank you! I hadn't considered that this was part of the problem. Much appreciated! 🙂