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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
normarc
Calcite | Level 5

/*

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 :SAS_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

7 REPLIES 7
Haikuo
Onyx | Level 15

Call Execute()

or DSUBL, if your SAS version supports it.

normarc
Calcite | Level 5

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

JUN_Sands
Calcite | Level 5

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

Thanks.

ballardw
Super User

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.

JUN_Sands
Calcite | Level 5

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

normarc
Calcite | Level 5

/*

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 :SAS_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;

normarc
Calcite | Level 5

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 1316 views
  • 6 likes
  • 4 in conversation