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!
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:
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
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;
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:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.