topic Re: Have PROC SQL within a Data Step?? in SAS Programming

Have PROC SQL within a Data Step??

DrZenith — Mon, 30 Jan 2012 21:00:13 GMT

Hey guys...

I want to have multiple PROC SQL statements to be run based on the value of a variable in a dataset.

e.g. If the value of the 'FLAG' is 1 then i want to run

PROC SQL A

create table example

sel * from example1 where key = FLAG

, if it is 2, then

PROC SQL B

create table example

sel * from example1 where key = FLAG

and so on...

I was planning to have PROC SQL within a data step, is it possible?? else what could be the best possible approach to this problem?

Thanks in advance!!

Re: Have PROC SQL within a Data Step??

Linlin — Mon, 30 Jan 2012 21:26:31 GMT

Hi,

Do you know Macro? is this helpful?

data have;

input flag;

cards;

;

run;

proc sql noprint;

select distinct flag into :flags separated by ' '

from have;

quit;

%let flags=&flags;

%macro test;

%do i=1 %to %sysfunc(countw(&flags));

%let flag=%scan(&flags,&i);

proc sql;

create table want&flag as

select * from sashelp.class

where age=&flag;

%end;

quit;

%mend;

%test

Linlin

Message was edited by:Linlin

Have PROC SQL within a Data Step??

Patrick — Mon, 30 Jan 2012 22:15:44 GMT

To propose "the best" approach is kind of hard as you don't really tell us what you have and what you want (have this data / need this output).

Are you eventually only looking for a way to split up a table based on some condition?

data outds0 outds1;

set example;

if flag=0 then output outds0;

else if flag=1 then output outds1;

run;

Re: Have PROC SQL within a Data Step??

LinusH — Mon, 30 Jan 2012 22:41:41 GMT

And another option could be using CALL EXECUTE...

Linus

Re: Have PROC SQL within a Data Step??

polingjw — Mon, 30 Jan 2012 23:47:32 GMT

It is possible to use PROC SQL within a DATA step. Take a look at my South Central SAS User's Group paper from last year: http://www.scsug.org/SCSUGProceedings/2011/poling1/SQL%20Function.pdf. While the code presented in the paper only executes a SELECT statement, I think that you could modify the code to execute a CREATE TABLE statement.