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