topic Re: Looping or using array within Proc sql in SAS Programming

Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 00:56:58 GMT

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

Re: Looping or using array within Proc sql

ShiroAmada — Thu, 30 Nov 2017 02:21:14 GMT

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;

Re: Looping or using array within Proc sql

PGStats — Thu, 30 Nov 2017 03:01:19 GMT

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;

Re: Looping or using array within Proc sql

ChrisNZ — Thu, 30 Nov 2017 03:06:39 GMT

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;

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 03:42:19 GMT

Thank you all very much for your quick and efficient replies.

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 03:45:36 GMT

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;

Re: Looping or using array within Proc sql

PGStats — Thu, 30 Nov 2017 03:52:20 GMT

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;

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 04:00:27 GMT

Thank you so much PGStats!

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 22:49:34 GMT

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

Re: Looping or using array within Proc sql

ChrisNZ — Thu, 30 Nov 2017 22:53:36 GMT

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

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 22:54:23 GMT

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

Re: Looping or using array within Proc sql

ChrisNZ — Thu, 30 Nov 2017 22:54:39 GMT

Macros are usually used as language generators.

As such they can be used anywhere.

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 22:59:32 GMT

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

Re: Looping or using array within Proc sql

q5pham — Thu, 30 Nov 2017 23:05:52 GMT

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

Re: Looping or using array within Proc sql

ChrisNZ — Fri, 01 Dec 2017 02:30:45 GMT

The error message is unambiguous:

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

Re: Looping or using array within Proc sql

q5pham — Fri, 01 Dec 2017 02:40:23 GMT

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

Re: Looping or using array within Proc sql

q5pham — Thu, 07 Dec 2017 00:10:01 GMT

Hi ChrisNZ,

I would like to use your code in a similar case, but I got errors. The tast is simply to scale down all columns (numeric format), except the first, by 100. Could you please help to correct for me. Thank you very much. Mai

proc contents
data = MP
out = test (keep = name varnum);
run;
proc sql ;
   select distinct name
    into : varlist separated by ' ' from test
   where varnum not in (1),
              %macro loop; %local i varname; %do i=1 %to dim(&varlist); %let varname=%scan(&varlist,&i);
        ,(&varname/100) as &varname
              %end; %mend; %loop
    from test

quit;

Re: Looping or using array within Proc sql

ChrisNZ — Thu, 07 Dec 2017 01:50:11 GMT

@q5pham Turn on option mprint then look at the invalid SQL query you are generating. The issue should become obvious.