BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8


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?

1 ACCEPTED SOLUTION

Accepted Solutions
chrej5am
Quartz | Level 8

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

View solution in original post

5 REPLIES 5
chrej5am
Quartz | Level 8

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

brophymj
Quartz | Level 8

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

LinusH
Tourmaline | Level 20

Create a view instead, an then have them dropped when creating the table.

Data never sleeps
brophymj
Quartz | Level 8

Could you give some lines of code to demonstrate how this might be done?

chrej5am
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 937 views
  • 0 likes
  • 3 in conversation