A quick query re this procedure...
I want to create a dataset from two other datasets, both of which have many columns of data. Both source datasets have 5 fields in common which I use to specify conditions i.e. where dataset1.field1 ge dataset2.field1, and so on. I select all fields by sepcifyuing select * as there are two many to list out but I get a green error message saying both fields contain the five fields in common which I use to set conditions. Is there a way of avoiding this? Can you select everything but drop five of the fields from one of the dataset?
Yes,
for example
proc sql;
create table TAB(drop=NEWVAR:)
as
select *
from DATASET1 as a inner join
DATASET2(rename=(VAR1=NEWVAR1 VAR2=NEWVAR2 VAR3=NEWVAR3 VAR4=NEWVAR4 VAR5=NEWVAR5)) as b
on a.VAR1=b.NEWVAR1;
;
quit;
Jakub
If you want to avoid the warning you can use the following code. It renames the variables causing the trouble first:
proc sql;
create table TAB
as
select *
from DATASET1 as a inner join
DATASET2(rename=(VAR1=NEWVAR1 VAR2=NEWVAR2 VAR3=NEWVAR3 VAR4=NEWVAR4 VAR5=NEWVAR5)) as b
on a.VAR1=b.NEWVAR1;
;
quit;
Jakub
Thanks chrej5am, and if you want to drop the new variables after they have served the purpose of setting the conditions where can you include the drop statement?
thanks
Create a view instead, an then have them dropped when creating the table.
Could you give some lines of code to demonstrate how this might be done?
Yes,
for example
proc sql;
create table TAB(drop=NEWVAR:)
as
select *
from DATASET1 as a inner join
DATASET2(rename=(VAR1=NEWVAR1 VAR2=NEWVAR2 VAR3=NEWVAR3 VAR4=NEWVAR4 VAR5=NEWVAR5)) as b
on a.VAR1=b.NEWVAR1;
;
quit;
Jakub
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.