BookmarkSubscribeRSS Feed
nanmz
Fluorite | Level 6

Hi everyone,

 

I have a dataset that looks something like this:

IDCCTransDateAmountAccountType
5555511-Jun-03114.56Savings
5555512-Jun-03119.56Savings
5555521-Jun-03-56.78Checking
555557-Jul-03359.31Savings
5555519-Jul-0389.56Checking
555553-Aug-031000Savings
777773-Dec-03645.21Savings
5555517-Aug-03-1200Checking

 

I want the final output to look something like this:

 

IDCCC6C7C8S6S7S8S12
55555-56.7889.56-1200234.12359.311000 
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!

 

 

2 REPLIES 2
Kurt_Bremser
Super User

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;
ballardw
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 407 views
  • 1 like
  • 3 in conversation