topic Re: Average data vertically in SAS in SAS Programming

Average data vertically in SAS

zhige50 — Fri, 14 Feb 2014 20:42:25 GMT

Hello,

I have a dataset looks like below, I am wondering if it is possible for SAS to average vertically. Anyone have any idea how to achieve that?

Original Dataset (example):

AcctNum Date Balance Other_Variable

1 201301 A

1 201302 B

1 201303 C

1 201304 D

1 201305 E

1 201306 F

1 201307 G

1 201308 H

1 201309 I

1 201310 J

1 201311 K

1 201312 L

1 201401 M XXXXX (want to keep)

2 201301 N

. . .

Dataset want to achieve (average balance for past12 month- avg(B-M))

Date Avg_Bal Other Variable

1 201401 XXX (average for the past 12 month) (Keep all other variable in row 201401)

2 201401 XXX (average for the past 12 month)

3 201401 XXX (average for the past 12 month)

Thanks in advance!

Regards,

Frank

Re: Average data vertically in SAS

ballardw — Fri, 14 Feb 2014 21:16:37 GMT

Some clarification looks like it is in order.

By past 12 months do you want the average from 201301 to 201312, 201302 to 201401 or a different period? Do all of the AcctNum have balances for the same period or are some shorter?

Do the values of the "other variables" change or are they the same for all of the records within the period you want to summarize? If they vary what is the rule for selection?

Re: Average data vertically in SAS

zhige50 — Fri, 14 Feb 2014 21:35:40 GMT

Sorry about the confusion. The 12 month is the month I want (in this example is 201401) backed 12 (so it is 201302-201401)

AcctNum Date Balance Variable1 Variable2

1 201301 20

1 201302 9

1 201303 1

1 201304 2

1 201305 5

1 201306 6

1 201307 10

1 201308 4 ......

1 201309 3 AB

1 201310 8 QC

1 201311 7 ON

1 201312 12 BC

1 201401 11 ON 999

2 201301 N

. . .

2 201401 XXX BC 457

. . .

Dataset want to achieve (average balance for past12 month- avg(B-M))

Date Avg_Bal Variable1 Variable2

1 201401 6.5 (1+2+....12)/12 ON 999

2 201401 XXX (average for the past 12 month) BC 457

3 201401 XXX (average for the past 12 month)

Re: Average data vertically in SAS

ballardw — Fri, 14 Feb 2014 21:46:12 GMT

Assuming all of the data is to summarized for the same period 201302-201401 (and not varing by last date in the data which is a moderately obnoxious kettle of fish)

Something like this might help

proc summary data=have nway; /* have is the name of the dataset you already have*/

where 201302 le date le 201401;

class id;

var date balance;

output out=want(drop = _type_ _freq_) max(date) = date mean(balance)=Avg_bal

maxid(date(variable1) date(variable2)) = variable1 variable2;

run;

These should get the values of variables 1 and 2 associated with the largest value of date within the period and the average balance.

If you don't drop the _freq_ you'll have a variable that says how many months (my assumption) of data were used to calculate the average.

Re: Average data vertically in SAS

zhige50 — Tue, 18 Feb 2014 15:28:34 GMT

Thanks so much ! One more question, I am wondering is there a way to also include the observation month balance (201301), but keep the average for following 12 month (201302-201401)?

Re: Average data vertically in SAS

ballardw — Tue, 18 Feb 2014 15:51:59 GMT

If you are going to want more than a single balance and maintain this going forward you need to be very specific and explicit about futher requirements such as what the resulting data set structure need to be.

For example does the 201301 balance need to be on the same record as the summaries above or a different record? How do you want to name the variable with that one month's balance in the new dataset?

Re: Average data vertically in SAS

zhige50 — Tue, 18 Feb 2014 16:09:52 GMT

The 201301 balance would be on the same record as the want output and it could be called Bal_Start_Mon

New dataset would be look like below (everything would be the same as what achieved in the previous code but one more variable want to be added (Bal_Start_Mon). Thanks in advance!

Acct_Num Date Avg_Bal Variable1 Variable2 Bal_Start_Mon (data from Column 201301)

1 201401 6.5 (1+2+....12)/12 ON 999 20

2 201401 XXX (average for the past 12 month) BC 457 .

Re: Average data vertically in SAS

ballardw — Tue, 18 Feb 2014 16:55:39 GMT

One way is merge the summarized data with the original value desired.

proc sql;

create table new_want as

select a.*, b.balance as Bal_Start_mon

from want as a left join

(select Acct_num, balance from have where date=201301) as b on

a.Acct_num=b.Acct_num;

quit;

Sql is often a preferred way to join data on conditions.

A data step merge could be done as well but often involves more filtering or termporary datasets.