topic Re: PROC SQL CREATE TABLE question in SAS Procedures

PROC SQL CREATE TABLE question

deleted_user — Fri, 04 Mar 2011 21:06:23 GMT

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;

Re: PROC SQL CREATE TABLE question

deleted_user — Fri, 04 Mar 2011 21:23:59 GMT

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

Re: PROC SQL CREATE TABLE question

DBailey — Sat, 05 Mar 2011 13:09:05 GMT

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.

Re: PROC SQL CREATE TABLE question

deleted_user — Sun, 06 Mar 2011 20:39:15 GMT

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?

Re: PROC SQL CREATE TABLE question

LinusH — Mon, 07 Mar 2011 13:32:03 GMT

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

Re: PROC SQL CREATE TABLE question

deleted_user — Mon, 07 Mar 2011 16:46:52 GMT

Hi Linus

Excellent worked perfectly!

Thanks - Joel