Looping or using array within Proc sql

[q5pham]

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

[ShiroAmada]

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;

[q5pham]

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

[PGStats · Accepted solution]

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;

[q5pham]

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;

[PGStats]

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;

[q5pham]

Thank you so much PGStats!

[ChrisNZ]

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;

[q5pham]

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

[ChrisNZ]

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

[ChrisNZ]

Macros are usually used as language generators.

As such they can be used anywhere.

[q5pham]

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

[q5pham]

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

[ChrisNZ]

The error message is unambiguous:

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

[q5pham]

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