topic Re: Proc SLQ; Create Table ... in SAS Procedures

Proc SLQ; Create Table ...

brophymj — Mon, 19 Jan 2015 13:56:13 GMT

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?

Re: Proc SLQ; Create Table ...

chrej5am — Mon, 19 Jan 2015 14:12:12 GMT

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

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

Re: Proc SLQ; Create Table ...

brophymj — Mon, 19 Jan 2015 14:15:41 GMT

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

Re: Proc SLQ; Create Table ...

LinusH — Mon, 19 Jan 2015 14:23:20 GMT

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

Re: Proc SLQ; Create Table ...

chrej5am — Mon, 19 Jan 2015 14:23:37 GMT

Yes,

for example

proc sql;

create table TAB(drop=NEWVAR:)

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

Re: Proc SLQ; Create Table ...

brophymj — Mon, 19 Jan 2015 14:29:15 GMT

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