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

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

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
abqdiane
Obsidian | Level 7
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
PROC Star

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
Obsidian | Level 7
thanks - when I get a chance I'll try to reformat it. I appreciate the
suggestions.
Diane

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 427 views
  • 2 likes
  • 3 in conversation