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!
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;
what about july+adult ?
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;
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.