Help using Base SAS procedures

Proc sql: dynamically selecting column based on input data

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Proc sql: dynamically selecting column based on input data

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!


Accepted Solutions
Solution
‎03-31-2015 10:46 PM
Contributor
Posts: 53

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

Thanks for Reeza's helpful answer. Smiley Happy

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. Smiley Sad

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

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

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

Thanks,
Jag
Super User
Posts: 19,868

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

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;

Solution
‎03-31-2015 10:46 PM
Contributor
Posts: 53

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

Thanks for Reeza's helpful answer. Smiley Happy

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. Smiley Sad

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 672 views
  • 5 likes
  • 3 in conversation