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.
http://www.ats.ucla.edu/stat/sas/library/nesug99/b
http://www.gasug.org/papers/DemystifyingMacro_Fech
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.
http://www.ats.ucla.edu/stat/sas/library/nesug99/b
http://www.gasug.org/papers/DemystifyingMacro_Fech
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.
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.