CODEWITHSUNDEEP
sas
Bob
Bob

Reputation: 756

Renaming Column with Dynamic Name

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?

Upvotes: 3

Views: 1162

Answers (2)

Chris J
Chris J

Reputation: 7769

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 ;

Upvotes: 2

Joe
Joe

Reputation: 63424

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

Upvotes: 3

Related Questions