topic Re: Sum Amount by ID, Month and Type in SAS Procedures

Sum Amount by ID, Month and Type

cirrus74 — Sun, 15 Mar 2015 16:24:22 GMT

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!

Re: Sum Amount by ID, Month and Type

naveen_srini — Sun, 15 Mar 2015 16:58:12 GMT

what about july+adult ?

Re: Sum Amount by ID, Month and Type

art297 — Sun, 15 Mar 2015 17:03:07 GMT

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;

Re: Sum Amount by ID, Month and Type

Reeza — Sun, 15 Mar 2015 22:43:35 GMT

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?

Re: Sum Amount by ID, Month and Type

cirrus74 — Mon, 16 Mar 2015 13:00:04 GMT

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.