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;
SQL doesn't have a DO loop.
SQL questions should be posted to the SAS Procedure forum: https://communities.sas.com/community/support-communities/sas_procedures
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.