BookmarkSubscribeRSS Feed
Fluorite | Level 6

Hi everyone,


I have a dataset that looks something like this:



I want the final output to look something like this:


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!



Super User

First, create the ACROSS variable:

data prepared;
set have;
cat = cats(substr(accounttype,1,1),month(transdate));

Then use it in the report:

proc report data=prepared;
column idcc amount,cat;
define idcc / group;
define amount / "" analysis sum;
define cat / "" across;
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=;

proc transpose data=summed out=want;
   by idcc;
   id cat;
   var amount;

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.;
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. '';

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.




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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