BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
q5pham
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

17 REPLIES 17
ShiroAmada
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Opal | Level 21

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
q5pham
Obsidian | Level 7

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
Opal | Level 21

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
q5pham
Obsidian | Level 7

Thank you so much PGStats!

ChrisNZ
Tourmaline | Level 20

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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Tourmaline | Level 20

Macros are usually used as language generators.

As such they can be used anywhere.

 

q5pham
Obsidian | Level 7

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
Obsidian | Level 7

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
Tourmaline | Level 20

The error message is unambiguous:

 

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

q5pham
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 17826 views
  • 6 likes
  • 4 in conversation