topic Re: Automate the Creation of Datasets w/ Macro Program in SAS Programming

Automate the Creation of Datasets w/ Macro Program

eparson — Tue, 02 May 2017 19:31:12 GMT

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

Re: Automate the Creation of Datasets w/ Macro Program

Kurt_Bremser — Tue, 02 May 2017 19:39:05 GMT

What for would you do that?

Re: Automate the Creation of Datasets w/ Macro Program

ballardw — Tue, 02 May 2017 19:43:58 GMT

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.

Re: Automate the Creation of Datasets w/ Macro Program

Reeza — Tue, 02 May 2017 20:27:17 GMT

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.

Re: Automate the Creation of Datasets w/ Macro Program

Astounding — Tue, 02 May 2017 20:42:25 GMT

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;

Re: Automate the Creation of Datasets w/ Macro Program

eparson — Tue, 02 May 2017 21:20:36 GMT

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.

Re: Automate the Creation of Datasets w/ Macro Program

Astounding — Wed, 03 May 2017 00:05:33 GMT

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.

Re: Automate the Creation of Datasets w/ Macro Program

Reeza — Wed, 03 May 2017 00:11:24 GMT

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

Re: Automate the Creation of Datasets w/ Macro Program

eparson — Wed, 03 May 2017 14:40:31 GMT

Re: Automate the Creation of Datasets w/ Macro Program

eparson — Wed, 03 May 2017 17:14:51 GMT

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.