BookmarkSubscribeRSS Feed
DrZenith
Calcite | Level 5

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

4 REPLIES 4
Linlin
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Tourmaline | Level 20

And another option could be using CALL EXECUTE...

Linus

Data never sleeps
polingjw
Quartz | Level 8

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 17096 views
  • 1 like
  • 5 in conversation