Proc SLQ; Create Table ...

[brophymj]

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?

[chrej5am]

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]

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]

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

[brophymj]

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

[chrej5am · Accepted solution]

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