Renaming Column with Dynamic Name

Question

I have a column name from a .xls file (using excelcs engine for importing) which is dynamic and changes from day to day.

I am wondering how to do reference and rename that dynamic column name within sas without knowing before hand what it will be called?

Answer 1

This depends some on how it dynamically changes. If it is entirely unpredictable - you cannot write code to figure it out, or to eliminate the other known columns - your simplest option may be to use GETNAMES=NO, and then set the names yourself.

If it is in some way predictable (such as it is "MYDYNAMIC_XXXX" where XXXX changes in some fashion), you can probably figure it out from dictionary.columns. (Modify libname/memname/etc. as appropriate; memname is the dataset name.)

proc sql;
 select name into :dynname
  from dictionary.columns
  where libname='WORK' and memname='MYDATASET'
    and name like 'MYDYNAMIC_%';
quit;

Alternately, you could use a NOT(IN(...)) clause to eliminate the known column names, if you need to know that.

Finally, if it is in one consistent location, easier than using GETNAMES=NO may be to query dictionary.columns based on the variable number (where varnum=5 for example if it is the fifth variable number).

Answer 2

Expanding on Joe's last comment - is the column is in the same position, just called something different?

If so, you can use the dictionary.columns table, selecting the specific column number, and storing the corresponding column name in a macro variable.

Example, your column is the 5th column in Excel/dataset...

/* Pull column name */
proc sql ;
  select name into :DYNVAR
  from dictionary.columns
  where libname = 'SASHELP'
    and memname = 'CLASS'
    and varnum  = 5 ;
quit ;

/* Then to reference the column simply substitute it for &DYNVAR */
data want ;
  set sashelp.class (keep=&DYNVAR) ;
run ;

You could then extend this to multiple columns if necessary...

/* Pull column name */
proc sql ;
  select name into :DYNVARS separated by ' '
  from dictionary.columns
  where libname = 'SASHELP'
    and memname = 'CLASS'
    and varnum  in (1,4,5) ;
quit ;

/* Then to reference the columns simply substitute it for &DYNVARS */
data want ;
  set sashelp.class (keep=&DYNVARS) ;
run ;