PROC SQL CREATE TABLE question

[deleted_user]

When using the code below it creates the table even if no records are returned. Is there a way to tell to to only create the table if records are returned as the result of the WHERE clause? I want to use the table creation itself as a trigger for other processes



PROC SQL;
CREATE TABLE work.STF_2001 AS SELECT
QUERY3723.Trigger
FROM WORK.QUERY3723 AS QUERY3723
WHERE QUERY3723.Trigger = 0;
QUIT;

[deleted_user]

Hi Joel,

I think you no need to mention Work library. If you dont save the dataset in permament library , default it will save it on work library.

Try this,
Proc Sql;
create table STF_2002
as
select Q.Trigger
from QUERY3723 AS Q where Q.Trigger=0;
Quit;

Thanks,
Raj

[DBailey]

I think the DDL to create the table happens before the query is actually performed. For some codes, that's a shortcut to duplicate the table structure without copying data. For example, if you say
create table TABA as select * from TABB where 1=2;

You will get a copy of the table structure without any data.

I would think you'll have to use macros to check and see if there are records that match your criteria and only then would you create the table.

[deleted_user]

that's what I was thinking -

I don't need a specifically a proc SQL -

a SAS data step would work as well -

but what I need is a macro that checks the tabel for records -

if there are some it creates TableA and if not TableB

anyone have a sample macro that does that?

[LinusH]

You could use something like:
proc sql;

%macro ConditionalCreate;

select count(*) into: macrovar
from a
where b=c
;
quit;

%if &macrovar ne 0 %then %do;

proc sql;
create table
...

;
quit;
%end;
%mend ConditionalCreate;

%ConditionalCreate;

/Linus

[deleted_user]

Hi Linus

Excellent worked perfectly!

Thanks - Joel