topic Re: iterating loop in sql in SAS Programming

iterating loop in sql

sasismylife — Thu, 21 Feb 2019 09:17:58 GMT

Hi Experts,

i am trying to iterate the below mentioned code. Code is running without any error but its not iterating. Tried changing the do statement before and after the Proc sql but not working.Could anyone please help out how to iterate the loop in sql.

Thanks for your help in advance.

proc sql;
select distinct sex into:gen separated by "~" from sashelp.class;
quit;

options symbolgen mprint;
%put &gen;

%macro prnt(var,i,upto);
%do s=&i %to &upto;
%let var=%scan(&gen,&i,'~');
Proc sql;
create table newt_&var as
select * from sashelp.class
where sex="&var";
quit;
%end;
%mend;


%prnt (&var,1,2);

Re: iterating loop in sql

s_lassen — Thu, 21 Feb 2019 09:28:01 GMT

Two problems with your code:

You used &i instead of &s in the %SCAN call
The first macro parameter is unnecessary

So, I think that hat you want to do is something like this:

proc sql;
select distinct sex into:gen separated by "~" from sashelp.class;
quit;
options symbolgen mprint;
%put &gen;
%macro prnt(i,upto);
  %local s var;
  %do s=&i %to &upto;
  %let var=%scan(&gen,&s,'~');
  Proc sql;
    create table newt_&var as
    select * from sashelp.class
    where sex="&var";
  quit;
  %end;
%mend;
%prnt (1,2);

Re: iterating loop in sql

sasismylife — Thu, 21 Feb 2019 09:39:34 GMT

Thank you Lassen for response. it helps me a lot 🙂

Re: iterating loop in sql

RW9 — Thu, 21 Feb 2019 09:45:51 GMT

And what is it your actually trying to achieve? Its almost never a good idea to split like data up, it incurs more read/write blocks, more header blocks on the file so more storage needs, creates a need for messy hard to maintain code etc. There is a functionality in Base SAS specifically for this type of grouped runs, it is called by group processing, and it is both faster and easier to code than trying to reproduce this yourself. So take your example, and add to it that your doing this to print m/f separately, you can simply do:

proc report data=sashelp.class;
  columns _all_;
  by sex;
  title "#byval1";
run;

The key part here is the by sex; which will automatically group the data into distinct groups based on the variables provided, and block out results for those distinct groups. The title just includes a means to show the distinct group in question in a title. This functionality is available to all procedures/datastep etc.

Don't try to re-invent the wheel.