Help using Base SAS procedures

Sum Amount by ID, Month and Type

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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 IDDateAmountType
12312/06/2013100Child
12314/06/2013200Adult
45601/07/2013-50Child
45610/08/2013250Adult

Which I would like to sort like this:

Account IDJune+ChildJuly+ChildJune+AdultAugust+Adult
123100 200
456 -50 250

Many thanks in advance!


Accepted Solutions
Solution
‎03-15-2015 01:03 PM
PROC Star
Posts: 7,363

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=_Smiley Happy sum=;

run;

proc transpose data=need2 out=want (drop=_Smiley Happy;

  by account_id;

  id combination;

  var amount;

run;

View solution in original post


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: 7,363

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=_Smiley Happy sum=;

run;

proc transpose data=need2 out=want (drop=_Smiley Happy;

  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: 17,837

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.

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

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