Solved
New Contributor
Posts: 2

# Sum Amount by ID, Month and Type

Hi All,

I'm new to SAS. I'm not quite sure how to do this and would appreciate some guidance. I have data like this:

 Account ID Date Amount Type 123 12/06/2013 100 Child 123 14/06/2013 200 Adult 456 01/07/2013 -50 Child 456 10/08/2013 250 Adult

Which I would like to sort like this:

 Account ID June+Child July+Child June+Adult August+Adult 123 100 200 456 -50 250

Many thanks in advance!

Accepted Solutions
Solution
‎03-15-2015 01:03 PM
PROC Star
Posts: 8,164

## Re: Sum Amount by ID, Month and Type

You mentioned SUM in the subject of your discussion, thus can we presume that you have multiple entries for given ids, dates and types? If so, something like the following should give you an idea of how to solve your problem:

data have;

informat date ddmmyy10.;

format date ddmmyy10.;

infile cards dlm=',';

input Account_ID Date Amount Type \$;

cards;

123,12/06/2013,100,Child

123,14/06/2013,200,Adult

456,01/07/2013,-50,Child

456,01/07/2013,100,Child

456,10/08/2013,250,Adult

;

data need;

set have;

month=month(date);

combination=catx('_',put(date,monname.),type);

run;

proc sort data=need;

by account_id month type;

run;

proc summary data=need nway;

by account_id combination notsorted;

var amount;

output out=need2 (drop=_ sum=;

run;

proc transpose data=need2 out=want (drop=_;

by account_id;

id combination;

var amount;

run;

All Replies
Frequent Contributor
Posts: 115

## Re: Sum Amount by ID, Month and Type

what about july+adult  ?

Solution
‎03-15-2015 01:03 PM
PROC Star
Posts: 8,164

## Re: Sum Amount by ID, Month and Type

You mentioned SUM in the subject of your discussion, thus can we presume that you have multiple entries for given ids, dates and types? If so, something like the following should give you an idea of how to solve your problem:

data have;

informat date ddmmyy10.;

format date ddmmyy10.;

infile cards dlm=',';

input Account_ID Date Amount Type \$;

cards;

123,12/06/2013,100,Child

123,14/06/2013,200,Adult

456,01/07/2013,-50,Child

456,01/07/2013,100,Child

456,10/08/2013,250,Adult

;

data need;

set have;

month=month(date);

combination=catx('_',put(date,monname.),type);

run;

proc sort data=need;

by account_id month type;

run;

proc summary data=need nway;

by account_id combination notsorted;

var amount;

output out=need2 (drop=_ sum=;

run;

proc transpose data=need2 out=want (drop=_;

by account_id;

id combination;

var amount;

run;

New Contributor
Posts: 2

## Re: Sum Amount by ID, Month and Type

Thank you very much for your help! Much appreciated!

And yes, sorry that I failed to clarify earlier, but there are multiple entries in the data which is why I specified sum in the title.

Super User
Posts: 23,771

## Re: Sum Amount by ID, Month and Type

Based on how SAS works this would make it harder to work with your data in the future, so I wouldn't recommend this data structure.

It would be harder to later make tally's for each month, quarter or year, by child/adult for example.

Or to filter out which values/records by amount per month i.e. how many accounts had a negative balance in July and belonged to a child?

And what about if you have more than one year of data?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 290 views
• 0 likes
• 4 in conversation