YTD information using sum in arrays

[claudiopcjr]

Hi all,

I have one ID column and twelve others columns that have monthly information of profit before taxes, which i will call here as PBT. My aim is to create twelve new columns with the year to date information of PBT, which means ((cumulated information up to intended date)/(number of months up to intended date))*12, all this in on single file. I already did this in different files using ARRAY through the code below within a MACRO statement:

DATA CUM_PBT_&MONTH. (DROP=i);
SET PBT_2018;
FORMAT PBT_YTD_&MONTH. COMMA19.2;
ARRAY PBT[*] '201801'n-"&MONTH."n;
DO i=1 to 12;
PBT_YTD_&MES.=(SUM(of '201801'n-"&MES."n)/DIM(BAI))*12;
END;
RUN;

How could I provide this code to have all these columns in a same file ?

To illustrate better what I want I attached a file that i did my objective (beggining at column 'O') information using excel from the original information (from column B to M).

[ballardw]

Providing code with macro variables and no idea what those macro variables may hold obscures the objective. Where does "&month" come from, what values does it take? Same for "&Mes".

Does the ID variable play any role in the calculations involved?

One suspects you are forcing an "Excel like" approach to the solution. It may be that your source data would be better off transformed into a long form with ID Month (as an actual date value) and "value".

Can you provide an example of what "have all these columns in a same file " would look like.

You will getter better responses if the example data is provided in the form of a data step so we can test code against it. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[SuryaKiran]

Hello,

Is this what your trying to,

data have;
input id $ val1 val2 val3 val4;
datalines;
A 1 2 3 4
B 2 4 5 6
C 3 4 5 6
;
run;

options symbolgen mprint mlogic;
%Macro test();

data want;
set have;
array val{4} val1-val4;
array PBT_Val{4} PBT_Val1-PBT_Val4;
%do i=1 %to 4;
PBT_Val(&i.)=(sum(of val1-val&i.)/&i)*12;
%end;
run;

%mend test;
%test;

[claudiopcjr]

Thanks @SuryaKiran for your answer, the code ran succesfully on the way that you wrote. Unfortunatelly the result didn't come out as expected, I believe it's because the variables specified on the terms: %do i=01 %to 12;

As you can check on the file that I attached there are 12 columns, one for each month, and the code doesn't seem recognizing the correct name of the months from 201802 till 201809. Is there a way to preserve the 0 on the left of number ?

Here's the code that I ran:

options symbolgen mprint mlogic;
%Macro test();

data want;
set have;
array val{*} '201801'n-'201812'n;
array PBT_Val{*} PBT_201801-PBT_201812;
%do i=01 %to 12;
PBT_Val(&i.)=(sum(of '201801'n-"2018&i."n)/&i)*12;
%end;
run;

%mend test;
%test;

The results are on the attached file ("WANT.xlsx").

Thanks!

[Astounding · Accepted solution]

You might find this easier if you got rid of the macro language entirely.  Let the DATA step process data instead.  For example:

data want;
set have;
array val{*} '201801'n-'201812'n;
array PBT_Val{*} PBT_201801-PBT_201812;

total=0;
do i=1 to 12;

   total + val{i};
   PBT_Val(&i.)=total/i*12;
end;

drop total;
run;

[claudiopcjr]

It worked perfectly thank you very much.