Help using Base SAS procedures

Proc SLQ; Create Table ...

Accepted Solution Solved
Reply
Super Contributor
Posts: 256
Accepted Solution

Proc SLQ; Create Table ...


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?


Accepted Solutions
Solution
‎01-19-2015 09:23 AM
Contributor
Posts: 42

Re: Proc SLQ; Create Table ...

Yes,

for example

proc sql;

create table TAB(drop=NEWVARSmiley Happy

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


All Replies
Contributor
Posts: 42

Re: Proc SLQ; Create Table ...

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

Super Contributor
Posts: 256

Re: Proc SLQ; Create Table ...

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

Super User
Posts: 5,254

Re: Proc SLQ; Create Table ...

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

Data never sleeps
Super Contributor
Posts: 256

Re: Proc SLQ; Create Table ...

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

Solution
‎01-19-2015 09:23 AM
Contributor
Posts: 42

Re: Proc SLQ; Create Table ...

Yes,

for example

proc sql;

create table TAB(drop=NEWVARSmiley Happy

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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