BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
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
1 ACCEPTED SOLUTION

Accepted Solutions
GertNissen
Barite | Level 11

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

View solution in original post

9 REPLIES 9
GertNissen
Barite | Level 11
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.
deleted_user
Not applicable
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
GertNissen
Barite | Level 11

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

deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
miguelito
Fluorite | Level 6
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 9 replies
  • 119114 views
  • 2 likes
  • 4 in conversation