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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.