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).