topic Re: Converting Date Values from Date Variable to new columns in SAS Programming

Converting Date Values from Date Variable to new columns

nanmz — Thu, 30 Mar 2023 06:52:36 GMT

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!

Re: Converting Date Values from Date Variable to new columns

Kurt_Bremser — Thu, 30 Mar 2023 07:15:51 GMT

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;

Re: Converting Date Values from Date Variable to new columns

ballardw — Thu, 30 Mar 2023 14:59:55 GMT

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.