Is it possible to create Temporart tables in SAS? 1

[liliya13]

Hi

I am a beginner in SAS and Data warehouse. I was said that I could NOT create intermediate table in Data warehouse DUE to no certain privileges and therefore I have to created multiple joins in one query (in only step within PROC sql). For example:

proc SQL;
create table home.ALL as

(select s.*, d.*, m.*
from tst s, prd_srv d, prd_cmp m
where m.id=s.id AND
(m.id=d.id AND
m.cntrl_key=d.cntrl_key) AND
s.rsn <> 'NO' AND
s.tag='G' AND
(m.cd='2' OR
m.cd='5')AND
d.type=2);
RUN;

I am looking for the way to do it in 3 steps (in other words to create 3 queries IN 3 DIFFERENT PROGRAMS(?) :
1.
PROC SQL;

create table home.step1 as
(select s.*
from tst s
where s.rsn <> 'NO' AND
s.tag='G')
RUN;

2.
PROC SQL;
create table home.step2 as
(select s.*, m.*
from tst s , prd_cmp m
where (m.id=s.id ) AND
(m.cd='2' OR
m.cd='5')AND
d.type=2);
run;

3.
PROC SQL;
create table home.step3 as
(select m.*, d.*
from prd_srv d , prd_cmp m

where (m.id=d.id AND
m.cntrl_key=d.cntrl_key);
run;

However It seems to me that there should be any way to create TEMPORARY tables in SAS BUT I do not know how to do it.

Could you give me a hand?

Thank you in advance,

Liliya

 

[klaz2002]

Have you tried to create 'work' tables? SAS usually requires that you have a place that is dedicated to temp tables, even Data Warehouse.

You could do the SQL code in 3 steps in the same SQL statement.
Take your code for example,
PROC SQL;

create table step1 as
(select s.*
from tst s
where s.rsn <> 'NO' AND
s.tag='G')

create table step2 as
(select s.*, m.*
from tst s , prd_cmp m
where (m.id=s.id ) AND
(m.cd='2' OR
m.cd='5')AND
d.type=2);

create table step3 as
(select m.*, d.*
from prd_srv d , prd_cmp m

where (m.id=d.id AND
m.cntrl_key=d.cntrl_key);
run;

You are left with three tables all you have to do is join them (thats if you want to).

I hope that this helps you. If you have any other questions just post.
Klaz