DATA Step, Macro, Functions and more

Have PROC SQL within a Data Step??

Reply
Occasional Contributor
Posts: 12

Have PROC SQL within a Data Step??

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

Super Contributor
Posts: 1,636

Re: Have PROC SQL within a Data Step??

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

Respected Advisor
Posts: 3,887

Have PROC SQL within a Data Step??

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;

Super User
Posts: 5,255

Re: Have PROC SQL within a Data Step??

And another option could be using CALL EXECUTE...

Linus

Data never sleeps
Regular Contributor
Posts: 171

Re: Have PROC SQL within a Data Step??

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.

Ask a Question
Discussion stats
  • 4 replies
  • 4166 views
  • 0 likes
  • 5 in conversation