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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.