Automate the Creation of Datasets w/ Macro Program

[eparson]

I am looking to create multiple datasets from city_variables dataset. There are a total of 58 observations that I summed up into macrovariable (&count) to stop the do loop. The city_variables dataset looks like (vertically ofcourse): CITY_NAME City1 City2 City3 City4 City5 City6 City7 City8 City9 City10 .......... City58 I created macrovariable &name from a data null statement in order to input the cityname into the dataset name. Any help would be great on how to automate the creation of the 48 files by name (not number). Thanks again. /Create macro with number of observations in concordinate file/ proc sql; select count(area_name); into :count from main.state_all; quit; %macro repeat; data _null_; set city_variables; %do i= 1 %UNTIL (i = &count); call symput('name',CITY_NAME); run; From this point I want to create some datasets using Proc sql statements linking to an oracle table proc sql; create table &name as..... from oracletable %end; %mend repeat; %repeat ------- I would like to be able to loop this process to create multiple datasets from the input dataset

[Kurt_Bremser]

What for would you do that?

[Reeza]

This is rarely a good way to process data and sends you do macro loop routes without a good reason. 90% of the time it's better to use some form of BY group processing.

Since you'll probably ignore this advice anyways, here's one posting that shows how to accomplish this:

http://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

This post goest into several variations with the same advice I mentioned - Best Practice is don't do this.

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

PS. Are you the same person who  asked this question on SO? The answers here will not be significantly different.

[eparson]

Because I have issues understanding %DO %TO as well as %DO %UNTIL and wanted to learn it more organically from being able to do it through a do loop from a real life example.  Of course there are more efficient ways which I will be implementing in the final program, but wanted to know how to do it this way.  

[Reeza]

Check the SAS 9.4 Macro Appendix (search here for link) that has a bunch of really good examples for getting started with Macros. 

[eparson]

Because I have issues understanding %DO %TO as well as %DO %UNTIL and wanted to learn it more organically from being able to do it through a do loop from a real life example.  Of course there are more efficient ways which I will be implementing in the final program, but wanted to know how to do it this way.

[ballardw]

Assuming you have made your connection to the (I assume Oracle database) then

data _null_;
   set city_variables;
   Call execute ("proc sql;
create table "||city_name||" as.....
from oracletable; quit;");
run;

where you actually provide the remaining code for the "...." part of proc sql.

Call execute can create code that is "stacked up" by the data step and executes after the data step runs. In effect creating as many calls to the sql portion are there are rows in the data set on the SET statement. This places the dataset variable value into the string submitted.

[Astounding]

I'm not sure if this would be faster, but it might be:

data _null_;

call execute('proc sql;');

do until (done);

   set city_variables end=done;

   call execute('create table ............');

end;

call execute('quit;');

stop;

run;

[eparson]

Thanks for adding this method.  I am still trying to wrap my head around this one and getting it to work.  I will continue to try to work this one, since I like the simplicity of the setup. However, I am currently experiencing numerous syntax issues that I will need to work through.  Thanks again.  

[Astounding · Accepted solution]

If you really must get practice with a %DO loop, here's one way to approach your question:

%macro repeat;

%local i;

%do i=1 %to &count;

   data _null_;

   set city_variables (firsobs=&i obs=&i);

   call symputx('name', city_name);

   run;

   proc sql;

   create table &name as ......;

   quit;

%end;

%mend repeat;

%repeat

Note that switching to CALL SYMPUTX will automatically strip out any leading or trailing blanks from the macro variable's value.