topic Store multiple values from a variable in different macros in SAS Programming

Store multiple values from a variable in different macros

Frank_johannes — Thu, 12 Jan 2023 19:20:57 GMT

Hi Everybody

I want to store multiple values containing datasets names for the use in a loop to import multiple sas files?

I have imported an excel file containing two rows: "Table" and "Select". For those rows where select is equal to "X" I want to create a macro containing the name in the table row?

Example dataset:

Table Select

Table1 x

Table2 x

Table3

Table4 x

I want to create multiple macros containing the values "Table1", "Table2" and "Table3" for the use in a macro loop to import these three datafiles in SAS format

Any suggestions?

Kind regards

Frank

Re: Store multiple values from a variable in different macros

PaigeMiller — Thu, 12 Jan 2023 19:31:18 GMT

You do not want macros. You want macro variables. These are not the same. Do not use the word "macro" to describe macro variables.

You say:

I want to create multiple macros containing the values "Table1", "Table2" and "Table3"

I assume you mean

I want to create multiple macro variables containing the values Table1, Table2 and Table4 (should have table 4 not table 3, with no quotes)

data _null_;
    set have end=eof;
    n=0;
    if select="X" then do;
        n+1;
        call symputx(cats('macrovar',n),table);
    end;
    if eof then call symputx('n',n);
run;

%put &=macrovar1;
%put &=macrovar2;
%put &=macrovar3;
%put &=n;

Re: Store multiple values from a variable in different macros

Tom — Thu, 12 Jan 2023 19:50:21 GMT

Why do you need to store the data into macro variables? Why not just use the data to drive the code creation directly.

data _null_;
  set example;
  where SELECT='X' ;
  call execute( ..... code generated using the value of TABLE ... );
run;

Or if you do need the list in macro variable why not just make ONE macro variable?

proc sql noprint;
select table into :tablelist separated by ' '
from example
where select='X'
;
%let ntables=&sqlobs;
quit;

If you really need to make multiple macro variable then use this SQL syntax.

proc sql noprint;
select table into :table1-
from example
where select='X'
;
%let ntables=&sqlobs;
quit;

Re: Store multiple values from a variable in different macros

Frank_johannes — Thu, 12 Jan 2023 20:05:50 GMT

Hi Paige,

Thank you for your reply and corrections to this topic.

My log says: WARNING: Apparent symbolic reference MACROVAR2 not resolved.

I have typed:

proc import datafile= "&path\test.xlsx"
out= tables
dbms= xlsx
replace;
sheet= "Tables";
run;

data _null_;
set tables end=eof;
n=0;
if select="x" then do;
n+1;
call symputx(cats('macrovar',n),table);
end;
if eof then call symputx('n',n);
run;

%put &=macrovar1;
%put &=macrovar2;
%put &=macrovar;
%put &=n;

How can I loop through the macro variables in a datastep afterwards?

// Frank

Re: Store multiple values from a variable in different macros

Tom — Thu, 12 Jan 2023 20:10:24 GMT

Macro variables will only be created for observations where the value of SELECT was a single lowercase x.

If it had an uppercase X or a space character followed by a lowercase x then it would not be "selected".

Re: Store multiple values from a variable in different macros

Reeza — Thu, 12 Jan 2023 20:23:58 GMT

data example;
infile cards truncover;
input table $ select $;
cards;
one x
two x
three 
four x
five 
six x
;;;;;
run;

proc sql noprint;
select table into :table1- 
from example
where select = 'x';
quit;

options mprint;
%let numTables = &sqlobs;

%put &numTables;
%put &Table1.;
%put &Table2.;
%put &&table&numTables.;

Log:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         data example;
 70         infile cards truncover;
 71         input table $ select $;
 72         cards;
 
 NOTE: The data set WORK.EXAMPLE has 6 observations and 2 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              523.31k
       OS Memory           20644.00k
       Timestamp           01/12/2023 08:22:38 PM
       Step Count                        59  Switch Count  2
       Page Faults                       0
       Page Reclaims                     131
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 79         ;;;;;
 
 80         run;
 81         
 82         proc sql noprint;
 83         select table into :table1-
 84         from example
 85         where select = 'x';
 86         quit;
 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              5456.81k
       OS Memory           25768.00k
       Timestamp           01/12/2023 08:22:38 PM
       Step Count                        60  Switch Count  0
       Page Faults                       0
       Page Reclaims                     72
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 87         
 88         options mprint;
 89         %let numTables = &sqlobs;
 90         
 91         %put &numTables;
 4
 92         %put &Table1.;
 one
 93         %put &Table2.;
 two
 94         %put &&table&numTables.;
 six
 95         
 96         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 106

@Frank_johannes wrote:

Hi Everybody

I want to store multiple values containing datasets names for the use in a loop to import multiple sas files?

I have imported an excel file containing two rows: "Table" and "Select". For those rows where select is equal to "X" I want to create a macro containing the name in the table row?

Example dataset:

Table Select

Table1 x

Table2 x

Table3

Table4 x

I want to create multiple macros containing the values "Table1", "Table2" and "Table3" for the use in a macro loop to import these three datafiles in SAS format

Any suggestions?

Kind regards

Frank

Re: Store multiple values from a variable in different macros

PaigeMiller — Thu, 12 Jan 2023 21:26:34 GMT

@Frank_johannes wrote:

Hi Paige,

Thank you for your reply and corrections to this topic.

My log says: WARNING: Apparent symbolic reference MACROVAR2 not resolved.

I have typed:

proc import datafile= "&path\test.xlsx"
out= tables
dbms= xlsx
replace;
sheet= "Tables";
run;

data _null_;
set tables end=eof;
n=0;
if select="x" then do;
n+1;
call symputx(cats('macrovar',n),table);
end;
if eof then call symputx('n',n);
run;

%put &=macrovar1;
%put &=macrovar2;
%put &=macrovar;
%put &=n;

How can I loop through the macro variables in a datastep afterwards?

// Frank

My mistake, the IF statement should test for lower case x