topic YTD information using sum in arrays in SAS Enterprise Guide

YTD information using sum in arrays

claudiopcjr — Fri, 08 Feb 2019 17:29:48 GMT

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).

Re: YTD information using sum in arrays

ballardw — Fri, 08 Feb 2019 17:39:36 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: YTD information using sum in arrays

SuryaKiran — Fri, 08 Feb 2019 18:18:20 GMT

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;

Re: YTD information using sum in arrays

claudiopcjr — Mon, 11 Feb 2019 13:59:30 GMT

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!

Re: YTD information using sum in arrays

Astounding — Mon, 11 Feb 2019 16:42:11 GMT

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;

Re: YTD information using sum in arrays

claudiopcjr — Mon, 11 Feb 2019 17:07:26 GMT

It worked perfectly thank you very much.