How to use a do loop here!

[goliPSU]

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

[Rick_SAS]

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]

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

[goliPSU]

Howles,

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

Sorry for any inconvenience,

[ballardw]

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]

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]

Missed the %end statement. Sorry.