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
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;
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;
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
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;
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;
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;
Thank you so much PGStats!
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;
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
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
Macros are usually used as language generators.
As such they can be used anywhere.
Hi ChrisNZ,
I tried to use your code without your tested data:
%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
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
The error message is unambiguous:
ERROR: The following columns were not found in the contributing tables: DAY, WK, YR.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: