topic Column name change dynamically in SAS Programming

Column name change dynamically

SASPhile — Mon, 31 Aug 2009 16:49:24 GMT

DATA multdat;
FORMAT date mmddyy10.;
INPUT id $ date mmddyy10. T1 T2 T3 T4 T5 T6;
CARDS;
A1 11/01/2004 223 204 195 196 197 200
A2 11/01/2004 211 192 183 196 197 200
;
run;

DATA univdat;
SET multdat;
ARRAY TEST{*} T1-T6;
KEEP id date RESULT;
DO i = 1 to dim(TEST);
RESULT=TEST(i);
OUTPUT;
END;
RUN;

The above code works fine as long as the number of variables for array name test remain the same.There could be less or more variables. next month I may get only 3 values T1 to T3? or I may get more than 6? How to assign an upper bound to the variable T?

Re: Column name change dynamically

Flip — Mon, 31 Aug 2009 17:01:03 GMT

How about something like this?

Proc sql;
select max(input(subtr(name, 2), best.) into : tlim
from dictionary.columns where libname = "WORK" and memname = "MULTDAT"
and substr(name , 1,1) = 'T';

then use &tlim

Warning: code off the top of my head may contain errors;}

Re: Column name change dynamically

SASPhile — Mon, 31 Aug 2009 17:15:46 GMT

Flip,
I tried this and it worked:

DATA multdat;
FORMAT date mmddyy10.;
INPUT id $ date mmddyy10. T1 T2 T3 T4 T5 T6 T7;
CARDS;
A1 11/01/2004 223 204 195 196 197 200 300
A2 11/01/2004 211 192 183 196 197 200 300
;
run;
proc sql;
select count(*) into:up from sashelp.vcolumn where libname='WORK' and memname='MULTDAT' and
name like '%T%';
quit;
%put &up.;
%macro try;
DATA univdat;
SET multdat;

ARRAY TEST{*} T1-%sysfunc(compress(T&up.));
KEEP id date RESULT;
DO i = 1 to dim(TEST);
RESULT=TEST(i);
OUTPUT;
END;
RUN;
%mend try;
%try;

Re: Column name change dynamically

sbb — Mon, 31 Aug 2009 17:41:35 GMT

Consider that if you have control over the SAS variables named in your DATA step and SAS dataset, you can define an ARRAY statement with a variable prefix, such as:

ARRAY ATNNN (*) T: ;

but you would not be able to have any other variables (not in the array nor of a different SAS variable type) defined in the SAS dataset.

Clearly this technique may or may not apply, but it is possible with some programming instances.

Scott Barry
SBBWorks, Inc.