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!!
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
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;
And another option could be using CALL EXECUTE...
Linus
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.