topic Re: Fixing macro values in SAS Programming

Fixing macro values

Batman — Thu, 05 Jun 2025 17:03:43 GMT

I'm trying to select values on a data set based on an extraction for search variables from another dataset. However, the macro doesn't retrieve any values (see rows 30-50. When I copy the macro-generated query from the log, it still doesn't work (see rows 51-57). However, when I when run it again after changing the where statement to single quotes (rows 58-66), it does work. However, I need double quotes in the macro code. What gives?

30 %macro importer;
31 /*extract values form 16th row of dataset*/
32 %let i=16;
33 data _null_;
34 set contraceptive_ingredient(firstobs=&i. obs=&i.);
35 call symputx('Cont_cat', 'Contraceptive Product Category'n);
36 call symputx('Act_Ing','Active Ingredients'n);
37 run;
38 %PUT &i &Cont_cat &Act_Ing;
39
40 /*Use selected values to create a macro array from larger dataset*/
41 proc sql noprint;
42 select distinct brand into: brands separated by ', '
43 from sel_drugs
44 where cont_cat="&Cont_cat"
45 and Act_Ing="&Act_Ing";
46 %put brands=&;
47
48 %mend;
49 %importer
MPRINT(IMPORTER): data _null_;
MPRINT(IMPORTER): set contraceptive_ingredient(firstobs=16 obs=16);
MPRINT(IMPORTER): call symputx('Cont_cat', 'Contraceptive Product Category'n);
MPRINT(IMPORTER): call symputx('Act_Ing','Active Ingredients'n);
MPRINT(IMPORTER): run;

NOTE: There were 1 observations read from the data set WORK.CONTRACEPTIVE_INGREDIENT.
NOTE: DATA statement used (Total process time):
2 The SAS System 11:36 Thursday, June 5, 2025

real time 0.01 seconds
cpu time 0.01 seconds

16 IUD (intrauterine device)/IUS (intrauterine system) without hormone (copper) LEVONORGESTREL
MPRINT(IMPORTER): proc sql noprint;
MPRINT(IMPORTER): select distinct brand into: brands separated by ', ' from sel_drugs where cont_cat="IUD (intrauterine
device)/IUS (intrauterine system) without hormone (copper)" and Act_Ing="LEVONORGESTREL";
brands=&
50
51 /*copied from log*/
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

52 proc sql noprint;

53 select distinct brand into: brands separated by ', ' from sel_drugs where cont_cat="IUD (intrauterine
54 device)/IUS (intrauterine system) without hormone (copper)" and Act_Ing="LEVONORGESTREL";
55 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

56 %put brands=&;
brands=&
57
58 /*changing double to single quotes*/
59 proc sql;
60 select distinct brand into: brands separated by ', ' from sel_drugs where cont_cat='IUD (intrauterine
61 device)/IUS (intrauterine system) without hormone (copper)' and Act_Ing='LEVONORGESTREL';
62 quit;
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

63 %put &Brands
64
65
66 GOPTIONS NOACCESSIBLE;
KYLEENA, LILETTA, MIRENA, SKYLA

Re: Fixing macro values

PaigeMiller — Thu, 05 Jun 2025 17:10:33 GMT

Please paste the log into the "code box" that appears when you click on the </> icon here. (And paste from the log window; do not paste from your message above). Using the "code box" aligns the text properly and enables us to see problems more easily. So doing this helps us, which helps you too.

Re: Fixing macro values

Batman — Thu, 05 Jun 2025 17:11:31 GMT

30         %macro importer;
31         /*extract values form 16th row of dataset*/
32         %let i=16;
33         data _null_;
34          set contraceptive_ingredient(firstobs=&i. obs=&i.);
35          call symputx('Cont_cat', 'Contraceptive Product Category'n);
36          call symputx('Act_Ing','Active Ingredients'n);
37         run;
38         %PUT &i &Cont_cat &Act_Ing;
39         
40         /*Use selected values to create a macro array from larger dataset*/
41         proc sql noprint;
42         	select distinct brand into: brands separated by ', '
43         	from sel_drugs
44         	where cont_cat="&Cont_cat"
45         	and Act_Ing="&Act_Ing";
46         %put brands=&;
47         
48         %mend;
49         %importer
MPRINT(IMPORTER):   data _null_;
MPRINT(IMPORTER):   set contraceptive_ingredient(firstobs=16 obs=16);
MPRINT(IMPORTER):   call symputx('Cont_cat', 'Contraceptive Product Category'n);
MPRINT(IMPORTER):   call symputx('Act_Ing','Active Ingredients'n);
MPRINT(IMPORTER):   run;

NOTE: There were 1 observations read from the data set WORK.CONTRACEPTIVE_INGREDIENT.
NOTE: DATA statement used (Total process time):
2                                                          The SAS System                               11:36 Thursday, June 5, 2025

      real time           0.01 seconds
      cpu time            0.01 seconds
      

16 IUD (intrauterine device)/IUS (intrauterine system) without hormone (copper) LEVONORGESTREL
MPRINT(IMPORTER):   proc sql noprint;
MPRINT(IMPORTER):   select distinct brand into: brands separated by ', ' from sel_drugs where cont_cat="IUD (intrauterine 
device)/IUS (intrauterine system) without hormone (copper)" and Act_Ing="LEVONORGESTREL";
brands=&
50         
51         /*copied from log*/
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      
52         proc sql noprint;

53         select distinct brand into: brands separated by ', ' from sel_drugs where cont_cat="IUD (intrauterine
54         device)/IUS (intrauterine system) without hormone (copper)" and Act_Ing="LEVONORGESTREL";
55         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

56         %put brands=&;
brands=&
57         
58         /*changing double to single quotes*/
59         proc sql;
60         select distinct brand into: brands separated by ', ' from sel_drugs where cont_cat='IUD (intrauterine
61         device)/IUS (intrauterine system) without hormone (copper)' and Act_Ing='LEVONORGESTREL';
62         quit;
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds
      

63         %put &Brands
64         
65         
66         GOPTIONS NOACCESSIBLE;
KYLEENA, LILETTA, MIRENA, SKYLA

Re: Fixing macro values

Quentin — Thu, 05 Jun 2025 17:12:52 GMT

I'm confused. Are you saying you get different results from running the below two bits of code, one with double quotes and one with single quotes? That would be very surprising.

*double quotes;
proc sql noprint;
  select distinct brand into: brands separated by ', ' 
  from sel_drugs 
  where cont_cat="IUD (intrauterine device)/IUS (intrauterine system) without hormone (copper)" and Act_Ing="LEVONORGESTREL"
;
%put &=brands;


*single quotes;
proc sql noprint;
  select distinct brand into: brands separated by ', ' 
  from sel_drugs 
  where cont_cat='IUD (intrauterine device)/IUS (intrauterine system) without hormone (copper)' and Act_Ing='LEVONORGESTREL'
;
%put &=brands;

Note when posting code, please click the "running man" insert SAS code button and paste your code into that window. It formats the code as code rather than as part of the text.

Re: Fixing macro values

Quentin — Thu, 05 Jun 2025 17:14:57 GMT

I see now. You have mistakenly used:

 %put brands=&;

instead of:

 %put &=brands;

Your %PUT statement does not reference a macro variable.

Re: Fixing macro values

Batman — Thu, 05 Jun 2025 17:18:01 GMT

Ugh, yes, thanks