07-16-2012 04:21 PM
I have the following Proc Sql to create sql tables, Instead of reapeating the commands I would like to know if I can use DO loop in proc SQL
create table sqldb.ACxC_1993 as
select * from CxC1993;
create table sqldb.AValuAdded_1993 as
select * from ValuAdded1993;
create table sqldb.AFinalDemand_1993 as
select * from FinalDemand1993;
create table sqldb.ACxC_1994 as
select * from CxC1994;
create table sqldb.AValuAdded_1994 as
select * from ValuAdded1994;
create table sqldb.AFinalDemand_1994 as
select * from FinalDemand1994;
create table sqldb.ACxC_1995 as
select * from CxC1995;
create table sqldb.AValuAdded_1995 as
select * from ValuAdded1995;
create table sqldb.AFinalDemand_1995 as
select * from FinalDemand1995;
07-17-2012 11:07 AM
You could use a macro to generate the desired code.
%do year = &start %to &end;
create table sqldb.ACxC_&year as
select * from CxC&year;
create table sqldb.AValuAdded_&year as
select * from ValuAdded&year;
create table sqldb.AFinalDemand_&year as
select * from FinalDemand&year;
However unless the number of statements saved is much larger than your example I'm not sure the possible obfuscation would be helpful. For 3 years I don't think I'd bother, if 15, OR if I was going to use this code for multiple sets of sequential years, such as 1994-96 1995-97 1996-98, then the macro would be more appropriated.
07-17-2012 11:36 AM
Thank you so much for your response,
I get the following error:
11469 %macro sqlloop(start=,end=);
11471 %do year = &start %to &end;
11473 create table sqldb.CxC_&year as
11475 select * from CxC&year;
11477 /* create table sqldb.AValuAdded_&year as
11479 select * from ValuAdded&year;
11481 create table sqldb.AFinalDemand_&year as
11483 select * from FinalDemand&year;*/
ERROR: There were 1 unclosed %DO statements. The macro SQLLOOP will not be compiled.
11491 proc sql;
WARNING: Apparent invocation of macro SQLLOOP not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.
Could not display help because connection to the remote browser failed.