topic Proc sql: dynamically selecting column based on input data in SAS Procedures

Proc sql: dynamically selecting column based on input data

iank131 — Wed, 01 Apr 2015 00:36:49 GMT

I want to dynamically select the column of an input data set in a PROC SQL procedure. I am sure it is an easy thing to do, but I am not sure how to do it. I have following code:

data index;

input date date9. A B C;

format date date9.;

datalines;

01JAN2015 1 2 3

02JAN2015 2 3 4

03JAN2015 3 4 5

;run;

data choice;

input id date date9. choice $;

format date date9.;

datalines;

1 02JAN2015 A

2 02JAN2015 B

3 03JAN2015 A

4 03JAN2015 C

; run;

proc sql;

create table choice_with_index as

select choice.*, index.*

from choice as a, index as b

where a.date = b.date; quit;

Instead of selecting all the columns of the index data set (which I do with select index.*), I only want to select the column that corresponds to the value of the variable choice in the choice data set. In other words, I only want the following data set:

data want;

input id date date9. choice $ chosen_index;

format date date9.;

datalines;

1 02JAN2015 A 2

2 02JAN2015 B 3

3 03JAN2015 A 3

4 03JAN2015 C 5

; run;

As I said, I suppose this is really easy to do. Could you also tell me what this technique is called?

Thanks for your help!

Re: Proc sql: dynamically selecting column based on input data

Jagadishkatam — Wed, 01 Apr 2015 01:17:15 GMT

Please try

data index;

input date date9. A B C;

format date date9.;

datalines;

01JAN2015 1 2 3

02JAN2015 2 3 4

03JAN2015 3 4 5

;

run;

proc transpose data=index out=index(rename=_name_=choice);

by date;

run;

data choice;

input id date date9. choice $;

format date date9.;

datalines;

1 02JAN2015 A

2 02JAN2015 B

3 03JAN2015 A

4 03JAN2015 C

;

run;

proc sql;

create table choice_with_index as

select choice.*,index.col1 as chosen_index

from choice as a left join index as b

on a.date = b.date and a.choice=b.choice;

quit;

Thanks,

Jag

Re: Proc sql: dynamically selecting column based on input data

Reeza — Wed, 01 Apr 2015 01:50:38 GMT

VVALUEX function is also an option, but the transpose is more succinct if you have a longer variable list to check:

data want;

merge choice (in=in_c) index;

by date;

if in_C;

choice_index=input(compress(vvaluex(choice)), 2.);

drop A B C;

run;

Re: Proc sql: dynamically selecting column based on input data

iank131 — Wed, 01 Apr 2015 02:46:32 GMT

Thanks for Reeza's helpful answer.

All I need to do is add to my original code following the PROC SQL is the following code that uses the VVALUEX function and I get the data set I want:

data want (drop= A B C);

set choice_with_index;

chosen_index = input( compress(vvaluex(choice)), 2.); run;

BTW, I tried putting it inside the PROC SQL, but I get an error since the VVALUEX function only works in data steps.

As I didn't know about the VVALUEX function, I found the following post most brief and informative in explaining it:

SAS TIP: Dynamic Variable Name — VValuex | TRAJECTORY