Is an array the best way to solve this problem

[abqdiane]

Hi,

I'm using SAS Enterprise Guide version 8.3.

Below is t

I have two data sets. One list is of teams. There are over 300 teams.  One dataset list the teams and the dollar amount they raised over the past 19 months. The other spreadsheet lists the same teams and the number of members they had each of those 19 months.  I was able to combine the two datasets into one so it has the following columns:

Team ID

Team Name

Month 1 amount raised (The variable is name is 202101.  This stands for the year and month YYYYMM  )

Month 2 amount raised (and on for 19 months) (The variable names are  202102 -202210 )

Number of members month 1  (The variable name is Jan2021)

number of members month 2 (The variable name is Feb2021)0

and on for 19 months.

I would like to calculate how much as raised each month on a per member basis for each team. This variable would be per member per month (pmpm) So I would be adding another 19 columns.

I started out with the following, but I then I wasn't sure what to do or even if an array is the best way to calculate the per member per month over the 19 months (Jan 2021 through August 2022)

data Amt_Raised_A;
set  Amt_raised_B;
array pmpm_array pmpm1 - pmpm19;
do i=1 to 19;
pmpm =

Any suggestions?  Thanks so much,

Diane

[PaigeMiller · Accepted solution]

How about this:

data Amt_Raised_A;
set  Amt_raised_B;
array pmpm_array pmpm1 - pmpm19;
array month 202101--202210;
array members jan2021--Oct2022;
do i=1 to 19;
pmpm(i) = month(i)/members(i);
end;
run;

This code is of course UNTESTED as I don't have your SAS data sets.

By the way, this is a great example of how this whole thing would be much simpler (no arrays needed) if you use a long data set (with a variable named MONTH) rather than a wide data set where the calendar information is part of the variable names. A good layout in Excel is not necessarily a good layout in SAS.

[abqdiane]

Wow - thanks so much I'll give it a try. Good point about the layout.
I'll let you know how it goes.
Diane

[SASKiwi]

Your data isn't really in the best shape for analysis. You need to transform your data from wide format, where your data is in separate variables for each month, to long format which would look like this:

Team_ID

Team_Name

Month - like 01Jan2021

Amount_Raised

Once you data is like this analysis is easy and you don't need arrays. Please supply some sample data if you want help transforming it..

[abqdiane]

thanks - when I get a chance I'll try to reformat it. I appreciate the
suggestions.
Diane