topic DO loop in PROC SQL. in SAS Programming

DO loop in PROC SQL.

deleted_user — Tue, 31 Mar 2009 09:18:59 GMT

This is what I would like to do:

PROC SQL;
DO k=1 TO 50;
CREATE TABLE NewTable(k) as
SELECT * FROM MyDataSet WHERE MyVar=k;
END;
QUIT;

I think you understand my intention.

Isn't DO allowed inside PROC SQL?

Another problem is the naming: NewTable(k).

How can I achieve what I want with short programming code?

In the real case the selection is much more complicated, but it is only the value of one variabel, corresponding to MyVar in the example, that is different in each loop step.

Regards
Anne

Re: DO loop in PROC SQL.

GertNissen — Tue, 31 Mar 2009 10:40:20 GMT

You can not make a loop inside SQL, but you can make a small macro that will do what you want.

[pre]%macro sqlloop;
PROC SQL;
%DO k=1 %TO 50;
CREATE TABLE NewTable&k. as
SELECT * FROM MyDataSet WHERE MyVar=&k.;
%END;
QUIT;
%mend;

%sqlloop;[/pre]

If you data are BIG, this will not be an efficient method as it will read the data 50 times - perhaps apply an index on MyDataSet on the variable MyVar to boot performance.

Re: DO loop in PROC SQL.

deleted_user — Tue, 31 Mar 2009 12:18:42 GMT

Thank you!

The purpose is to make smaller sets from MyDataSet to get data for different years from which I want to get various statistics.
So I don't really think putting an index on MyDataSet would solve this. It's the MyDataSet(k) I want. I call them NewTable(k).

I have no experience with macros.

Would this be possible:

%macro sqlloop;
PROC SQL;
%DO k=1 %TO 50;
Year=k+1949;
CREATE TABLE NewTable&k. as SELECT * FROM MyDataSet WHERE Anno=Year;
%END;
QUIT;
%mend;

%sqlloop;

Would it even be possible delete the do loop from the macro and instead call the macro in a do loop with k as a parameter?

But from where should the calling be done in that case? Calling from inside a data step maybe is not a good idea, if even possible.

Regards
Anne

Re: DO loop in PROC SQL.

GertNissen — Thu, 22 Sep 2016 19:18:11 GMT

Putting an index on MyDataSet would make it much faster to read.

--Would it even be possible delete the do loop from the macro and instead call the macro in a do loop with k as a parameter?

Yes, that would be possible - but have a look at this alternate solution

%macro sqlloop(start,end); 
   PROC SQL; 
     %DO year=&start. %TO &end.; 
       CREATE TABLE NewTable&year. as 
       SELECT * FROM MyDataSet WHERE anno=&year.; 
     %END; 
   QUIT;
%mend; 

%sqlloop(start=1949, end=1999)

This is just to show another approach, where data is only read 2 times (instead of 50 times)

%macro sqlloop(data,byvar);
   proc sql NOPRINT;
     select distinct &byvar. into :_values SEPARATED by ' _'
     from &data.;
   quit; 
   data _&_values.;
     set &data.;
      select (&byvar);
       %do i=1 %to %sysfunc(count(_&_values.,_));
          %let var = %sysfunc(scan(_&_values.,&i.)); 
          when ("%substr(&var.,2)") output &var.;
       %end;
       end;
   run;
%mend;

%sqlloop(data=sashelp.class, byvar=age)
%sqlloop(data=sashelp.class, byvar=sex)
%sqlloop(data=MyDataSet, byvar=anno)

@Cynthia_sas also adds:

One way to code repetitive tasks or create generic code is to use Macro processing. There are several ways to use the SAS Macro facility, from the simple &macvar reference (such as you would use in a title or footnote) to a complex macro program that is defined and then invoked as %macpgm(parm1=val1); (as just one example).

I find that this paper is a good introduction to the macro facility and walks you through much of the basic understanding you will need to approach these tasks:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

And then these papers are also aimed at macro beginners:
http://www2.sas.com/proceedings/sugi29/243-29.pdf
http://www2.sas.com/proceedings/sugi26/p066-26.pdf
http://www.nesug.org/Proceedings/nesug03/bt/bt009.pdf
http://www.ats.ucla.edu/stat/sas/library/nesug99/bt046.pdf
http://www.gasug.org/papers/DemystifyingMacro_Fecht.pdf

Re: DO loop in PROC SQL.

deleted_user — Wed, 01 Apr 2009 07:27:07 GMT

Many thanks!

I still don't understand what you are getting at by proposing an index on MyDataSet. MyDataSet is my original data set.

Your second code alternative I will leave for supplementary studies.

Well, then I have my 50 datasets, NewTable(1) - NewTable(50). I want to do the same kind of statistics on each dataset. Of course I don't want to code 50 new data steps:
DATA Stat1; SET NewTable(1); /* some code */ run;
...

DATA Stat50; SET NewTable(50); /* some code */ run;

Is it necessary to write a macro for that purpose also?

Regards
Anne

Re: DO loop in PROC SQL.

Cynthia_sas — Wed, 01 Apr 2009 16:43:49 GMT

Hi:
One way to code repetitive tasks or create generic code is to use Macro processing. There are several ways to use the SAS Macro facility, from the simple &macvar reference (such as you would use in a title or footnote) to a complex macro program that is defined and then invoked as %macpgm(parm1=val1); (as just one example).

I find that this paper is a good introduction to the macro facility and walks you through much of the basic understanding you will need to approach these tasks:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

And then these papers are also aimed at macro beginners:
http://www2.sas.com/proceedings/sugi29/243-29.pdf
http://www2.sas.com/proceedings/sugi26/p066-26.pdf
http://www.nesug.org/Proceedings/nesug03/bt/bt009.pdf
http://www.ats.ucla.edu/stat/sas/library/nesug99/bt046.pdf
http://www.gasug.org/papers/DemystifyingMacro_Fecht.pdf

cynthia

Re: DO loop in PROC SQL.

deleted_user — Thu, 02 Apr 2009 07:29:51 GMT

The Stroupe paper was very pedagogic and instructive.

I have only one question:

On page 3, in the upper half of the right column, you can read:

To invoke the macro definition, use:
%MacroName
Notice there is no semicolon at the end of the %VACATION
statement. It is wise not to put a semicolon at the end of the
macro call in case you end a program statement prematurely..

Could you elaborate on that, please.

Regards
Anne

Re: DO loop in PROC SQL.

deleted_user — Thu, 02 Apr 2009 09:52:04 GMT

Hi Anne,

You may have created a piece of code which simply writes one word, for example a variable name, which you place in the middle of a SAS code statement, putting a colon after the macro call would end that statement. For example:

%macro what_variable(a_or_b=);
%if &a_or_b=a %then %do;
variable_b
%end;
%else %do;
variable_a
%end;
%mend what_variable;

Then using it you might have:

data output(keep=%what_variable(a_or_b=b)
variable 1 variable2);
set input;
run;

WHich would resolve to

data output(keep=variable_b variable 1 variable2);
set input;
run;

If you put a semi-colon after the macro call....

data output(keep=%what_variable(a_or_b=b);
variable 1 variable2);
set input;
run;

Which would resolve to

data output(keep=variable_b; variable 1 variable2);
set input;
run;

Which would give you an error.

That is an extreme example but I think that's what they are getting at.

Personally if I know the macro is going to complete the statement it's self then I put a semi colon in simply because if I don't it mucks up the colours in the editor.

You hvae to know what the macro is doing though.

Re: DO loop in PROC SQL.

Cynthia_sas — Thu, 02 Apr 2009 14:21:27 GMT

Hi, Anne:
Generally, I agree with the statement in the paper, if I know that my macro is going to only generate part of a statement and I also know that my closing semi-colon is going to be correctly provided elsewhere. However, if I know that my macro is going to generate whole statements and complete steps, then I always end my macro call with a semi-colon.

One reason I got into this habit is that when I was coding programs for SAS/IntrNet and for stored processes, most macro calls needed to have a delimiter after them and without going into too much detail, if a macro did NOT have any parameters, the only way to provide a delimiter was to do:
%macroname() OR %macroname;

I believe in thoroughly testing and debugging my starter, non-macroized, SAS program first, so I know whether it's appropirate to put a semicolon at the end of the macro call or not.

cynthia

Re: DO loop in PROC SQL.

miguelito — Tue, 17 Jan 2017 17:18:34 GMT

Hi,
The first piece of code is pretty straight forward since it loops from 1949 to 1999, each year. However, i do not know how the second proposition relates to that.

I am wondering how we use the idea of the first piece of code, but instead of printing separate tables for each year, generate only one table containing all the years in one column and the whatever we SELECT to do in a second column. Thanks!