Is there a way to scan multiple columns for a specific value and output the column name?

0
votes

I have a table with about 30 columns. The columns can have values of X,N,C,D. Is there a way short of writing individual case statements to scan all the columns for a specific value, in this case C, and if a column does have that value in a row then output the column name to a new column? So for instance:

COL1| COL2 |COL3....|COL30 |NEW_COL

'X' | 'C' | 'N' | 'D' | 'COL2'

Where Column2 in this row has the value I'm searching for, 'C', so the value of 'New_column' is 'COL2'.

Hopefully that makes sense. Thanks for all your time!

1
sas

1 Answers

4
votes

This should give you an idea. WhichC returns the index of the first argument that matches arguments 2,3,.... VNAME returns the name of the variable.

25         data _null_;
26            array col[4] $1 ('X' 'C' 'N' 'D');
27            l = whichc('C',of col[*]);
28            length cColName $32;
29            if l then cColname = vname(col[l]);
30            put _all_;
31            run;

col1=X col2=C col3=N col4=D l=2 cColName=col2 _ERROR_=0 _N_=1