topic Re: How to use a do loop here! in SAS/IML Software and Matrix Computations

How to use a do loop here!

goliPSU — Mon, 16 Jul 2012 20:21:57 GMT

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

Re: How to use a do loop here!

Rick_SAS — Mon, 16 Jul 2012 20:27:27 GMT

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

Re: How to use a do loop here!

ballardw — Tue, 17 Jul 2012 15:07:43 GMT

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.

Re: How to use a do loop here!

goliPSU — Tue, 17 Jul 2012 15:36:45 GMT

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.

Re: How to use a do loop here!

Howles — Tue, 17 Jul 2012 16:10:19 GMT

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

Re: How to use a do loop here!

goliPSU — Tue, 17 Jul 2012 16:46:46 GMT

Howles,

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

Sorry for any inconvenience,

Re: How to use a do loop here!

ballardw — Tue, 17 Jul 2012 17:17:20 GMT

Missed the %end statement. Sorry.