Do
Hi,
I have 5 different location names stored in dataset.
Location Name | SAS Name |
---|---|
London | qw |
Chicago | re |
Las Vegas | wd |
Tokyo | ft |
New York | gt |
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.
/*
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;
Call Execute()
or DSUBL, if your SAS version supports it.
Please provide some sample data for each of your datasets and more details about what you would like the code to do.
Added more explanation what I have been trying to do.. Hopefully, that makes sense.
Thanks.
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.
Is it possible that I can create/link macro parameter from the location table which is updated automatically on a regular basis?
/*
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.