BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eparson
Fluorite | Level 6

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Reeza
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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
PROC Star

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
Fluorite | Level 6

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
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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