Solved
Contributor
Posts: 34

# Looping or using array within Proc sql

[ Edited ]

Hi Communities,

I hope you could help me to solve the problem below.

Here is my code:

proc sql;
create table IPw  as
select *, (exp(sum(log(1+Food/100))))-1 as Food1 format=percent8.2
from _temp
group by yr, wk
having day=max(day)
order by dates ;
quit;

My _temp table has 30 columns named by different industries (e.g. Food as can bee seen from previous code), and I need to apply the same operation, e.g calculated (exp(sum(log(1+Food/100)))) -1, to the rest of 29 columns. I'm thinking about creating an array variable which store values of my 30 columns, but I cannot work it out.

Looping the Proc sql seems to be slow as the table has to be read 30 times.

Could you please suggest a new code.

Many thanks,

Mai

Accepted Solutions
Solution
‎11-29-2017 10:33 PM
Posts: 5,403

## Re: Looping or using array within Proc sql

Something like this :

``````proc sort data=_temp; by yr wk day; run;

data ipw;
array s food1 power1 ... music1; /* Match industry variable list */
do until(last.wk);
set _temp; by yr wk;
array ind food -- music;
do i = 1 to dim(ind);
s{i} = sum(s{i}, log(1+(ind{i}/100)));
end;
end;
do i = 1 to dim(ind);
s{i} = exp(s{i}) - 1;
end;
drop i;
run;``````
PG

All Replies
Frequent Contributor
Posts: 113

## Re: Looping or using array within Proc sql

Try this, i hope I understand you correctly.

``````%let n=3; *You need to change this to 30;

*Note: You have to populate n1 to n30 and use each of your 30 variables;

%let n1=Food;
%let n2=Beverage;
%let n3=Dunno;

%macro Test;
%do i=1 %to &n;
proc sql;
create table IPw_&i  as
select *, (exp(sum(log(1+&&n&i. / 100))))-1 as &&n&i._1 format=percent8.2
from _temp
group by yr, wk
having day=max(day)
order by dates ;
quit;
%end;
%mend;
options mprint;
%Test;

* Or you can do this, so you only have 1 output table

%macro Test_Take2;
%do i=1 %to &n;
%if %eval(&n=1) %then %do;
proc sql;
create table IPw  as
select *, (exp(sum(log(1+&&n&i. / 100))))-1 as &&n&i._1 format=percent8.2
from _temp
group by yr, wk
having day=max(day)
order by dates ;
quit;
%end;

%if %eval(&n>1) %then %do;
proc sql;
create table IPw  as
select *, (exp(sum(log(1+&&n&i. / 100))))-1 as &&n&i._1 format=percent8.2
from IPw
group by yr, wk
having day=max(day)
order by dates ;
quit;
%end;
%end;
%mend;

options mprint;
%Test_Take2;``````
Contributor
Posts: 34

## Re: Looping or using array within Proc sql

Hi Shiro,

Thanks for your responses. I had the same thought as shown your approach initially, however I was reluctant as my table has to be re-read 30 times.

Mai

Solution
‎11-29-2017 10:33 PM
Posts: 5,403

## Re: Looping or using array within Proc sql

Something like this :

``````proc sort data=_temp; by yr wk day; run;

data ipw;
array s food1 power1 ... music1; /* Match industry variable list */
do until(last.wk);
set _temp; by yr wk;
array ind food -- music;
do i = 1 to dim(ind);
s{i} = sum(s{i}, log(1+(ind{i}/100)));
end;
end;
do i = 1 to dim(ind);
s{i} = exp(s{i}) - 1;
end;
drop i;
run;``````
PG
Contributor
Posts: 34

## Re: Looping or using array within Proc sql

[ Edited ]

Hi PGStats,

Since I don't need values of existing columns Food--music, instead of creating new array S of food1,.., music1, I would like to re-use these variables, so i use ind{i} instead of s{i}. That also saves me typing 30 elements of the array s.  But the result are not right. Any hints? Thanks, Mai

``````data ipw;

do until(last.wk);
set _temp; by yr wk;
array ind food -- music;
do i = 1 to dim(ind);
ind{i} = sum(ind{i}, log(1+(ind{i}/100)));
end;
end;
do i = 1 to dim(ind);
ind{i} = exp(ind{i}) - 1;
end;
drop i;
run;``````

Posts: 5,403

## Re: Looping or using array within Proc sql

Try this:

``````proc sort data=_temp; by yr wk day; run;

data ipw;
array _s{99};
do until(last.wk);
set _temp; by yr wk;
array ind food -- music;
do i = 1 to dim(ind);
_s{i} = sum(_s{i}, log(1+(ind{i}/100)));
end;
end;
do i = 1 to dim(ind);
ind{i} = exp(_s{i}) - 1;
end;
drop i _s: ;
run;``````
PG
Contributor
Posts: 34

## Re: Looping or using array within Proc sql

Thank you so much PGStats!

PROC Star
Posts: 2,231

## Re: Looping or using array within Proc sql

Like this?

``````%* Create test data;
data HAVE;
retain FOOD 1 WATER 1 SODA 0 JUNKFOOD 0 YR 1 WK 1 DAY 1 DATES 1;
run;

%let varlist= FOOD WATER SODA JUNKFOOD;
option mprint;

proc sql;
create table IPW  as
select *
%macro loop; %local i varname; %do i=1 %to 4; %let varname=%scan(&varlist,&i);
,(exp(sum(log(1+&varname/100))))-1 as &varname.1 format=percent8.2
%end; %mend; %loop
from HAVE
group by YR, WK
having DAY=max(day)
order by DATES ;
quit;``````

Contributor
Posts: 34

## Re: Looping or using array within Proc sql

Hi ChrizNZ,

The syntax for a macro running inside proc sql is new to me. Do you have a link for a detailed guideline. I tried to use your code, but got a syntax error. Thanks, Mai

PROC Star
Posts: 2,231

## Re: Looping or using array within Proc sql

I just copied and pasted the code and it runs fine.

25
26 %* Create test data;
27 data HAVE;
28 retain FOOD 1 WATER 1 SODA 0 JUNKFOOD 0 YR 1 WK 1 DAY 1 DATES 1;
29 run;

NOTE: The data set WORK.HAVE has 1 observations and 8 variables.
NOTE: Compressing data set WORK.HAVE increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 479.06k
OS Memory 48232.00k
Timestamp 12/01/2017 11:51:51 AM
Step Count 188 Switch Count 34

30
31 %let varlist= FOOD WATER SODA JUNKFOOD;
32 option mprint;
33
34 proc sql;
35 create table IPW as
36 select *
2 The SAS System 10:50 Wednesday, November 29, 2017

37 %macro loop; %local i varname; %do i=1 %to 4; %let varname=%scan(&varlist,&i);
38 ,(exp(sum(log(1+&varname/100))))-1 as &varname.1 format=percent8.2
39 %end; %mend; %loop
MLOGIC(LOOP): Beginning execution.
MLOGIC(LOOP): %LOCAL I VARNAME
MLOGIC(LOOP): %DO loop beginning; index variable I; start value is 1; stop value is 4; by value is 1.
MLOGIC(LOOP): %LET (variable name is VARNAME)
SYMBOLGEN: Macro variable VARLIST resolves to FOOD WATER SODA JUNKFOOD
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable VARNAME resolves to FOOD
SYMBOLGEN: Macro variable VARNAME resolves to FOOD
MLOGIC(LOOP): %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(LOOP): %LET (variable name is VARNAME)
SYMBOLGEN: Macro variable VARLIST resolves to FOOD WATER SODA JUNKFOOD
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable VARNAME resolves to WATER
SYMBOLGEN: Macro variable VARNAME resolves to WATER
MLOGIC(LOOP): %DO loop index variable I is now 3; loop will iterate again.
MLOGIC(LOOP): %LET (variable name is VARNAME)
SYMBOLGEN: Macro variable VARLIST resolves to FOOD WATER SODA JUNKFOOD
SYMBOLGEN: Macro variable I resolves to 3
SYMBOLGEN: Macro variable VARNAME resolves to SODA
SYMBOLGEN: Macro variable VARNAME resolves to SODA
MLOGIC(LOOP): %DO loop index variable I is now 4; loop will iterate again.
MLOGIC(LOOP): %LET (variable name is VARNAME)
SYMBOLGEN: Macro variable VARLIST resolves to FOOD WATER SODA JUNKFOOD
SYMBOLGEN: Macro variable I resolves to 4
SYMBOLGEN: Macro variable VARNAME resolves to JUNKFOOD
SYMBOLGEN: Macro variable VARNAME resolves to JUNKFOOD
MLOGIC(LOOP): %DO loop index variable I is now 5; loop will not iterate again.
MPRINT(LOOP): * ,(exp(sum(log(1+FOOD/100))))-1 as FOOD1 format=percent8.2 ,(exp(sum(log(1+WATER/100))))-1 as WATER1
format=percent8.2 ,(exp(sum(log(1+SODA/100))))-1 as SODA1 format=percent8.2 ,(exp(sum(log(1+JUNKFOOD/100))))-1 as JUNKFOOD1
format=percent8.2
MLOGIC(LOOP): Ending execution.
40 from HAVE
41 group by YR, WK
42 having DAY=max(day)
43 order by DATES ;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compressing data set WORK.IPW increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.IPW created, with 1 rows and 12 columns.

44 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 9664.34k
OS Memory 55268.00k
Timestamp 12/01/2017 11:51:51 AM
Step Count 189 Switch Count 134

PROC Star
Posts: 2,231

## Re: Looping or using array within Proc sql

Macros are usually used as language generators.

As such they can be used anywhere.

Contributor
Posts: 34

## Re: Looping or using array within Proc sql

Hi ChrisNZ,

%let varlist= FOOD BEER SMOKE;
option mprint;

proc sql;
create table IPW  as
select *
%macro loop; %local i varname; %do i=1 %to 4; %let varname=%scan(&varlist,&i);
,(exp(sum(log(1+&varname/100))))-1 as &varname.1 format=percent8.2
%end; %mend; %loop
from IP
group by YR, WK
having DAY=max(day)
order by DATES ;
quit;

And here is the log file:

NOTE: Line generated by the invoked macro "LOOP".
34                  ,(exp(sum(log(1+&varname/100))))-1 as &varname.1 format=percent8.2
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

NOTE: Line generated by the macro variable "VARNAME".
34          1
_
22
76
MPRINT(LOOP):   * ,(exp(sum(log(1+FOOD/100))))-1 as FOOD1 format=percent8.2 ,(exp(sum(log(1+BEER/100))))-1 as BEER1
format=percent8.2 ,(exp(sum(log(1+SMOKE/100))))-1 as SMOKE1 format=percent8.2 ,(exp(sum(log(1+/100) )))-1 as 1 format=percent8.2
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

35           from IP
36           group by YR, WK
37           having DAY=max(day)
38           order by DATES ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
2                                                          The SAS System                            16:48 Monday, November 27, 2017

39         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time           0.00 seconds
cpu time            0.00 seconds

Contributor
Posts: 34

## Re: Looping or using array within Proc sql

Ooops, I forgot to change the loop. The correct log file should be:

25         GOPTIONS ACCESSIBLE;
26         %let varlist= FOOD BEER SMOKE;
27         option mprint;
28

29         proc sql;
30           create table IPW  as
31           select *
32                       %macro loop; %local i varname; %do i=1 %to 3; %let varname=%scan(&varlist,&i);
33                 ,(exp(sum(log(1+&varname/100))))-1 as &varname.1 format=percent8.2
34                       %end; %mend; %loop
MPRINT(LOOP):   * ,(exp(sum(log(1+FOOD/100))))-1 as FOOD1 format=percent8.2 ,(exp(sum(log(1+BEER/100))))-1 as BEER1
format=percent8.2 ,(exp(sum(log(1+SMOKE/100))))-1 as SMOKE1 format=percent8.2
35           from IP
36           group by YR, WK
37           having DAY=max(day)
38           order by DATES ;
ERROR: The following columns were not found in the contributing tables: DAY, WK, YR.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
39         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time           0.00 seconds
cpu time            0.00 seconds

PROC Star
Posts: 2,231

## Re: Looping or using array within Proc sql

The error message is unambiguous:

ERROR: The following columns were not found in the contributing tables: DAY, WK, YR.

Contributor
Posts: 34

## Re: Looping or using array within Proc sql

Hi ChrisNZ,

I found a mistake in my table. The code is running as desired.

Thanks for sharing another way of solving my issue.

Cheers, Mai

☑ This topic is solved.