Have PROC SQL within a Data Step??

[DrZenith]

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!!

[Linlin]

Hi,

Do you know Macro? is this helpful?

data have;

input flag;

cards;

12

12

13

16

14

15

;

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

[Patrick]

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;

[LinusH]

And another option could be using CALL EXECUTE...

Linus

[polingjw]

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.