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; ... View more

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 ... View more

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 ... View more

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 ... View more

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 ... View more

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 ... View more

Thank you so much PGStats! ... View more

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;   ... View more

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

Thank you very much Reeza. ... View more

Thanks Reeza for spotting out my typo mistakes. I have corrected, meaning i have 4 variables Method1-Method4 and 4 variable Var1-Var4 only.     ... View more

Thank Rick for your help, as always. ... View more