Help using Base SAS procedures

Macro Loop

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Macro Loop

Do

Hi,

I have 5 different location names stored in dataset.

Location Name

SAS Name

Londonqw
Chicagore
Las Vegaswd
Tokyoft
New Yorkgt

And I have 5 different datasets in each locations. I am trying to use one sas code with using Macro Do Loop through the 'SAS_Name' column.

For example, I have this sample code for each Location Names, but I would try to create tables based on SAS Names.

%macro.....;

Proc SQL;

Create table library.qw_ranking as

Select...

From work.qw_ranking;

Quit;

%mend.....

I would like to save some space and time on writing one code doing loop over the SAS_Name from the dataset instead of writing the same codes for all location,

Is there a way to do this?

Thanks.


Accepted Solutions
Solution
‎04-30-2015 08:12 PM
Occasional Contributor
Posts: 5

Re: Macro Loop

/*

Something like the following will run dynamically depending on the records in the Location table.   Notice the commented query, if you want you could use the Location_Name for the name of the generated output tables;

This is one of the generated queries it will run:

MPRINT(PROCESS):   create table Sum_qw as select column1, sum( column2 ) as scolumn2 from qw group by column1;

MPRINT(PROCESS):   quit;

*/

* Given a similar Location table;

data Location;
id = 1; Location_Name = 'London   '; SAS_Name = 'qw'; output;
id = 2; Location_Name = 'Chicago  '; SAS_Name = 're'; output;
id = 3; Location_Name = 'Las Vegas'; SAS_Name = 'wd'; output;
id = 4; Location_Name = 'Tokyo    '; SAS_Name = 'ft'; output;
id = 5; Location_Name = 'New York '; SAS_Name = 'gt'; output;
run;

proc sql noprint;
select SAS_Name into Smiley FrustratedAS_Name_List separated by ' '
from location
order by id;
quit;

proc sql print;
select translate( trim( substr( Location_Name, 1, 28 ) ), '_', ' ' ) into :Location_Name_List separated by ' '
from location
order by id;
quit;

%macro process;
%let i = 1;
%let SAS_Name = %scan( &SAS_Name_List, &i );
%let Location_Name = %scan( &Location_Name_List, &i );
%do %until( &SAS_Name = );

  proc sql;
  create table Sum_&SAS_Name as
  select column1, sum( column2 ) as scolumn2
  from &SAS_Name
  group by column1;
  quit;

/*
  proc sql;
  create table Sum_&Location_Name as
  select column1, sum( column2 ) as scolumn2
  from &SAS_Name
  group by column1;
  quit;
*/

  %let i = %eval( &i + 1 );
%let SAS_Name = %scan( &SAS_Name_List, &i );
%let Location_Name = %scan( &Location_Name_List, &i );
%end;

%mend;

%process;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Macro Loop

Call Execute()

or DSUBL, if your SAS version supports it.

Occasional Contributor
Posts: 5

Re: Macro Loop

Please provide some sample data for each of your datasets and more details about what you would like the code to do. 

Contributor
Posts: 22

Re: Macro Loop

Added more explanation what I have been trying to do.. Hopefully, that makes sense.

Thanks.

Super User
Posts: 10,497

Re: Macro Loop

1) Make the value a parameter for the macro:

example stub:

%macro mymacro (name= );

Proc SQL;

Create table library.&name._ranking as

Select...

From work.qw_ranking;

Quit;

%mend.....

2) in a data step build the string that the macro should use for parameters and submit with call execute:

data _null_;

     set have;

     call execute ('%mymacro(name =' || sasnamevariable || ');' );

run;

should call the macro one for each value of your sas name variable in the data set. Depending on the complexity of your parameters for the macro you might want to build the entire string a single variable instead of multiple concatenation operators within the Call Execute statement.

You can search this forum for "call execute" and find a number of examples.

Contributor
Posts: 22

Re: Macro Loop

Is it possible that I can create/link macro parameter from the location table which is updated automatically on a regular basis?

Solution
‎04-30-2015 08:12 PM
Occasional Contributor
Posts: 5

Re: Macro Loop

/*

Something like the following will run dynamically depending on the records in the Location table.   Notice the commented query, if you want you could use the Location_Name for the name of the generated output tables;

This is one of the generated queries it will run:

MPRINT(PROCESS):   create table Sum_qw as select column1, sum( column2 ) as scolumn2 from qw group by column1;

MPRINT(PROCESS):   quit;

*/

* Given a similar Location table;

data Location;
id = 1; Location_Name = 'London   '; SAS_Name = 'qw'; output;
id = 2; Location_Name = 'Chicago  '; SAS_Name = 're'; output;
id = 3; Location_Name = 'Las Vegas'; SAS_Name = 'wd'; output;
id = 4; Location_Name = 'Tokyo    '; SAS_Name = 'ft'; output;
id = 5; Location_Name = 'New York '; SAS_Name = 'gt'; output;
run;

proc sql noprint;
select SAS_Name into Smiley FrustratedAS_Name_List separated by ' '
from location
order by id;
quit;

proc sql print;
select translate( trim( substr( Location_Name, 1, 28 ) ), '_', ' ' ) into :Location_Name_List separated by ' '
from location
order by id;
quit;

%macro process;
%let i = 1;
%let SAS_Name = %scan( &SAS_Name_List, &i );
%let Location_Name = %scan( &Location_Name_List, &i );
%do %until( &SAS_Name = );

  proc sql;
  create table Sum_&SAS_Name as
  select column1, sum( column2 ) as scolumn2
  from &SAS_Name
  group by column1;
  quit;

/*
  proc sql;
  create table Sum_&Location_Name as
  select column1, sum( column2 ) as scolumn2
  from &SAS_Name
  group by column1;
  quit;
*/

  %let i = %eval( &i + 1 );
%let SAS_Name = %scan( &SAS_Name_List, &i );
%let Location_Name = %scan( &Location_Name_List, &i );
%end;

%mend;

%process;

Occasional Contributor
Posts: 5

Re: Macro Loop

WOW!   I had forgotten the power and beauty of Call Execute!   Only 3 or 4 lines of code...   All driven by the contents of the Location table.

data _null_;  set location;

call execute( 'Proc Sql; Create Table Sum_'||translate( trim( Location_Name ), '_', ' ' )||

               ' as Select column1, Sum( column2 ) as Scolumn2 From '||SAS_Name||' Group By Column1; Quit;' );

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 524 views
  • 6 likes
  • 4 in conversation