Using Macro variables to define a variable list

Reply
Contributor
Posts: 27

Using Macro variables to define a variable list


Say I have:

data anyfile;

retain ID_fld _0 _1 _2 _3 _4 _5 _6 _7 _8 _9 _10;

set anyfile;

run;

To simplify, I could also write:

data anyfile;

retain ID_fld _0-_10;

set anyfile;

run;

This works fine, except, I may not know how many columns to I have in the given dataset (say after a transpose). So I can do a lookup of the number of columns in the table, and assign the value (minus the number of non _N form columns) to a macro variable, say &ncolumns.

I would like to write:

data anyfile;

retain ID_fld _0-_&ncolumns;

set anyfile;

run;

But this does not work. Any ideas?

I get: Missing numeric suffix on a numbered variable list (_0-_).

Super User
Posts: 10,500

Re: Using Macro variables to define a variable list

You likely have a leading space in &ncolumns or a space snuck into your executed code. How did you assign the value to ncolumns?

A test using

%let ncolumns=3;

then your data step should confirm that the general logic is fine.

Contributor
Posts: 27

Re: Using Macro variables to define a variable list

Thanks for the reply, it must be with my macro variable assignment, because  using say %let ncolumns = 100; I get it to work.

I'm doing a transpose, so I use SQL to get the maximum number in the column being transposed:

proc sql noprint;

select max(ln_age) into :ncolumns

from anyfile_before_transpose;

quit;


ln_age is a number type column.

Super Contributor
Posts: 339

Re: Using Macro variables to define a variable list

You can build the var list in a macro using the following:

proc transpose data=something out=work.temp;

...

run;

%let libname=work;

%let transposeddsname=temp;

proc sql;

     select name

     into :collist separated by ' '

     from sashelp.vcolumn

     where libname=&libname

          and memname=&transposeddsname

          and PRXMATCH("m/^ID_fld|_[0-9]+$/i", -1, trim(name)) GT 0;

quit;

I added the /i option in case the ID_fld column gets parsed with different case in the vtable.

From there, you should be able to do

data anyfile;

     set anyfile;

     retain &collist;

run;

Super User
Posts: 17,824

Re: Using Macro variables to define a variable list

You can add the prefix option to your transpose, so your data is named col_1 or sample_1 and then use the colon modifier:

retain col_:;

Contributor
Posts: 27

Re: Using Macro variables to define a variable list

Got the answer I was looking for:

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543554.htm

The leading zeroes were not being trimmed.

By changing my code to:

proc sql noprint;

select max(ln_age) into :ncolumns seperated by ''

from anyfile_before_transpose;

quit;

I resolved the issue. Simply using: %put &ncolumns was not displaying the error.

By trying: %let abc = _0-_&ncolumns; %put &abc; I was able to see how it was actually being handled.

Super User
Super User
Posts: 6,500

Re: Using Macro variables to define a variable list

Here are multiple ways to eliminate the padding.

2    proc sql noprint ;

3      select max(age) into :a1 from sashelp.class;

4    %put a1=|&a1|;

a1=|      16|

5    %let a1=&a1;

6    %put a1=|&a1|;

a1=|16|

7

8      select max(age) into :a1 - :a1 from sashelp.class;

9    %put a1=|&a1|;

a1=|16|

10

11     select max(age) into :a1 separated by 'anything' from sashelp.class;

12   %put a1=|&a1|;

a1=|16|

13

14   * SAS 9.3 or higher ;

15     select max(age) into :a1 -  from sashelp.class;

16   %put a1=|&a1|;

a1=|16|

17

18     select max(age) into :a1 trimmed from sashelp.class;

19   %put a1=|&a1|;

a1=|16|

20   quit;

Ask a Question
Discussion stats
  • 6 replies
  • 296 views
  • 1 like
  • 5 in conversation