Hi everyone,
I have a dataset that looks something like this:
IDCC | TransDate | Amount | AccountType |
55555 | 11-Jun-03 | 114.56 | Savings |
55555 | 12-Jun-03 | 119.56 | Savings |
55555 | 21-Jun-03 | -56.78 | Checking |
55555 | 7-Jul-03 | 359.31 | Savings |
55555 | 19-Jul-03 | 89.56 | Checking |
55555 | 3-Aug-03 | 1000 | Savings |
77777 | 3-Dec-03 | 645.21 | Savings |
55555 | 17-Aug-03 | -1200 | Checking |
I want the final output to look something like this:
IDCC | C6 | C7 | C8 | S6 | S7 | S8 | S12 |
55555 | -56.78 | 89.56 | -1200 | 234.12 | 359.31 | 1000 | |
77777 | 645.21 |
where C6 - Checking for Jun, C7 - Checking for Jul, C8 - Checking for Aug
and S6 - Savings for Jun, S7- Savings for Jul, S8- Savings for Aug, S12- Savings for Dec.
My question is, how do I rename the new columns based on the desired output? I understand I can just sort the data by IDC, account type and date, do a proc transpose and rename the variables accordingly. But I intend on using macro to rename the variables to the desired output. Any input is very much appreciated. Thanks in advance!
First, create the ACROSS variable:
data prepared;
set have;
cat = cats(substr(accounttype,1,1),month(transdate));
run;
Then use it in the report:
proc report data=prepared;
column idcc amount,cat;
define idcc / group;
define amount / "" analysis sum;
define cat / "" across;
run;
And if you really think that you need a data set this may work, using the data set @Kurt_Bremser suggests:
proc summary data=prepdate nway; class idcc cat; var amount; output out=summed(drop=_:) sum=; run; proc transpose data=summed out=want; by idcc; id cat; var amount; run;
The summary step is to get a single total for amount for the Cat groups within the ID. Transpose places it into the wide format you requested.
Caution: You do not show any data for more than a few months and does show more than one calendar year. IF your data actually comes from more than one year then your shown desired format could well include values from more than one year in a month. So you may want to rethink this.
Do note that you can use date values as groups just using the proper format and may be a more useful approach for reports:
data have; input IDCC TransDate :anydtdte. Amount AccountType $; format transdate ddmmyy10.; datalines; 55555 11-Jun-03 114.56 Savings 55555 12-Jun-03 119.56 Savings 55555 21-Jun-03 -56.78 Checking 55555 7-Jul-03 359.31 Savings 55555 19-Jul-03 89.56 Checking 55555 3-Aug-03 1000 Savings 77777 3-Dec-03 645.21 Savings 55555 17-Aug-03 -1200 Checking ; proc report data=have; columns idcc Accounttype ,Transdate,amount; define idcc /group; define accounttype/across '' ; define transdate/across format=monyy7. ''; run;
OR you could summarize using the AccountType and Transdate with the MONYY7, or other year and month format, to get the totals then add the cat variable using the month and year info before transposing
Lots of other ways to do things depending on what comes next.
Please not the data step above. That is the way to provide example data so we do not have to guess properties of variables and can test code against data similar to yours. Paste the code into a text box opened on the forum with the </> icon above the message window as the forum software will reformat text pasted into the main windows and may result in code that will not run.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.