BookmarkSubscribeRSS Feed
goliPSU
Calcite | Level 5

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

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;

quit;

run;

Thank you

6 REPLIES 6
Rick_SAS
SAS Super FREQ

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

Howles
Quartz | Level 8

and questions which have already been posted should not be re-posted with a different subject.

goliPSU
Calcite | Level 5

Howles,

I believe it has been posted in different discussion groups as Rick recommended.

Sorry for any inconvenience,

ballardw
Super User

You could use a macro to generate the desired code.

%macro sqlloop(start=,end=);

     %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;

    %end;

%mend;

proc sql;

%sqlloop(start=1993,end=1995)

quit;

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.

goliPSU
Calcite | Level 5

Hi Ballardw,

Thank you so much for your response,

I get the following error:

11465  run;

11466

11467

11468

11469  %macro sqlloop(start=,end=);

11470

11471       %do year = &start %to &end;

11472

11473       create table sqldb.CxC_&year as

11474

11475       select * from CxC&year;

11476

11477      /* create table sqldb.AValuAdded_&year as

11478

11479       select * from ValuAdded&year;

11480

11481        create table sqldb.AFinalDemand_&year as

11482

11483       select * from FinalDemand&year;*/

11484

11485

11486

11487  %mend;

ERROR: There were 1 unclosed %DO statements.  The macro SQLLOOP will not be compiled.

11488

11489

11490

11491  proc sql;

11492

11493  %sqlloop(start=1993,end=1995)

       -

       180

WARNING: Apparent invocation of macro SQLLOOP not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

11494

11495  quit;

Could not display help because connection to the remote browser failed.

ballardw
Super User

Missed the %end statement. Sorry.

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!

Multiple Linear Regression in SAS

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.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 6 replies
  • 1439 views
  • 0 likes
  • 4 in conversation