08-16-2013 10:31 AM
Say I have:
retain ID_fld _0 _1 _2 _3 _4 _5 _6 _7 _8 _9 _10;
To simplify, I could also write:
retain ID_fld _0-_10;
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:
retain ID_fld _0-_&ncolumns;
But this does not work. Any ideas?
I get: Missing numeric suffix on a numbered variable list (_0-_).
08-16-2013 10:52 AM
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
then your data step should confirm that the general logic is fine.
08-16-2013 11:06 AM
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
ln_age is a number type column.
08-16-2013 11:30 AM
You can build the var list in a macro using the following:
proc transpose data=something out=work.temp;
into :collist separated by ' '
and PRXMATCH("m/^ID_fld|_[0-9]+$/i", -1, trim(name)) GT 0;
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
08-16-2013 11:38 AM
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:
08-16-2013 12:02 PM
Got the answer I was looking for:
The leading zeroes were not being trimmed.
By changing my code to:
proc sql noprint;
select max(ln_age) into :ncolumns seperated by ''
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.
08-16-2013 01:05 PM
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|;
5 %let a1=&a1;
6 %put a1=|&a1|;
8 select max(age) into :a1 - :a1 from sashelp.class;
9 %put a1=|&a1|;
11 select max(age) into :a1 separated by 'anything' from sashelp.class;
12 %put a1=|&a1|;
14 * SAS 9.3 or higher ;
15 select max(age) into :a1 - from sashelp.class;
16 %put a1=|&a1|;
18 select max(age) into :a1 trimmed from sashelp.class;
19 %put a1=|&a1|;