DATA Step, Macro, Functions and more

Automate the Creation of Datasets w/ Macro Program

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Automate the Creation of Datasets w/ Macro Program


 

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

Accepted Solutions
Solution
‎05-03-2017 03:36 PM
Super User
Posts: 5,495

Re: Automate the Creation of Datasets w/ Macro Program

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.

View solution in original post


All Replies
Super User
Posts: 7,758

Re: Automate the Creation of Datasets w/ Macro Program

What for would you do that?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,768

Re: Automate the Creation of Datasets w/ Macro Program

Posted in reply to KurtBremser

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.

 

Occasional Contributor
Posts: 5

Re: Automate the Creation of Datasets w/ Macro Program

Posted in reply to KurtBremser

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.  

Super User
Posts: 19,768

Re: Automate the Creation of Datasets w/ Macro Program

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

Occasional Contributor
Posts: 5

Re: Automate the Creation of Datasets w/ Macro Program

Posted in reply to KurtBremser

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.

Super User
Posts: 11,336

Re: Automate the Creation of Datasets w/ Macro Program

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.

Super User
Posts: 5,495

Re: Automate the Creation of Datasets w/ Macro Program

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;

Occasional Contributor
Posts: 5

Re: Automate the Creation of Datasets w/ Macro Program

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.  


Solution
‎05-03-2017 03:36 PM
Super User
Posts: 5,495

Re: Automate the Creation of Datasets w/ Macro Program

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 371 views
  • 6 likes
  • 5 in conversation