topic Ordering dates pursuant to proc transpose function in SAS Programming

Ordering dates pursuant to proc transpose function

hnb_matt_d — Thu, 26 Jan 2023 18:36:53 GMT

I have a dataset that has counts of activity types grouped by month and year (for a 13 month period). This dataset will be compiled monthly (at the beginning of each month for the preceding 13 months), so the immediately preceding month (December, in January's instance) will have two (2) appearances. One for the month immediately preceding (December 2022) and one for 13 months ago (December 2021).

I've also been tasked with re-creating the last seven (7) months' data (June 2022 through December 2022). Everything is working outside the proc transpose not ordering dates (months/years - or however this needs to be formatted) chronologically from left to right. More specifically, March 2022 continually appears as the last month listed (to the right) with every other month/year appearing in order as wanted.

data volumes;
set volumes_1;
format month_2 monyy.;
month_2 = input(month !! "01",yymmdd8.);
run;

^This code creates a new variable, month_2, in a date format. Full disclosure, I've run the proc transpose (included below) using a date formatted month_2 and a character formatted month variable with the same results (March 2022 not cooperating and appearing in order).

proc sort data=volumes (drop=month);
by transaction_type_code month_2;
run;


proc transpose data=volumes out=PIVOT (drop=_:);
by transaction_type_code;
id month_2;
var volume;
run;

^There's the sort and subsequent proc transpose, to result in:

I've utilized multiple date formats for month_2, with none of them working properly. HELP!

Re: Ordering dates pursuant to proc transpose function

data_null__ — Thu, 26 Jan 2023 19:03:33 GMT

Create a dummy level of

transaction_type_code=.

with missing value and one record for each MONTH in the correct order.

Re: Ordering dates pursuant to proc transpose function

ballardw — Thu, 26 Jan 2023 19:07:56 GMT

If you don't have all of the months in every level of the by variable, such as your transaction_type_code variable then that is likely. The order of appearance of the ID values sets which order the variable appears.

I generally don't worry about the order of variables in a data set as anytime people need to read things you specify the order in the report generating code.

You can quite often complicate a task by placing actual date into variable names like this.

Without actual data or an example of what you need for the output it is a tad difficult to suggest another approach.

Re: Ordering dates pursuant to proc transpose function

PaigeMiller — Thu, 26 Jan 2023 19:59:54 GMT

If the goal is to create a report or an Excel output with calendar names as the column headings, then PROC TRANSPOSE simply is not the way to go. There is nothing in PROC TRANSPOSE that will force the result into calendar order.

Instead you want to use PROC REPORT with an ACROSS variable and the option ORDER=INTERNAL. If you do this, then this problem you are having goes away, and furthermore doesn't involve you creating a transposed data set. EXAMPLE

Re: Ordering dates pursuant to proc transpose function

SASJedi — Thu, 26 Jan 2023 19:31:36 GMT

Since you didn't provide sample data, I took the liberty of creating some to play with:

/*Make fake data*/
data volumes;
	call streaminit(12345);
	do transaction_type_code = "CHECK","CASH","CARD";
		DO YEAR=2021 TO 2022;
			do Month=1 TO 12;
				month_2 = input(CATS(YEAR,PUT(month,Z2.),"01"),yymmdd8.);
				Volume=rand('INTEGER',10,1000);
				IF month_2 >= '01DEC2021'd then output;
			end;
		end;
	end;
format month_2 monyy.; DROP YEAR;
run;

This produced an input data set that looked like this:

Then I ran your code against that data. The only mod I did was to drop YEAR, which you didn't have in your original data:

proc sort data=volumes (drop=month year);
	by transaction_type_code month_2;
run;

proc transpose data=volumes out=PIVOT (drop=_:);
   by transaction_type_code;
   id month_2; 
   var volume;
run;

This is the result from SAS 9.4 M7, Windowing environment (PC SAS):

From Enterprise Guide 8.3 running on SAS 9.4M7 (I run in dark mode)

And from Viya 2023. (also in dark mode):

I can't seem to replicate your results using your code. Is it possible to share a few rows of the original volumes data set without violating privacy?

Re: Ordering dates pursuant to proc transpose function

hnb_matt_d — Thu, 26 Jan 2023 19:47:56 GMT

Working with this version...

Re: Ordering dates pursuant to proc transpose function

hnb_matt_d — Thu, 26 Jan 2023 19:59:54 GMT

Can't really share the specific transaction_type _code. I can tell you there are ~400 of them with ~2.5 billion occurrences across those 400 in the last 13 months.

Also, this will be an automated code execution and output, so I'd rather not hard-code dates/years. Need this to be as self-sustainingly dynamic as possible.

Re: Ordering dates pursuant to proc transpose function

PaigeMiller — Thu, 26 Jan 2023 20:00:41 GMT

@hnb_matt_d wrote:

Need this to be as self-sustainingly dynamic as possible.

PROC REPORT! I have already provided a small example.

Re: Ordering dates pursuant to proc transpose function

hnb_matt_d — Thu, 26 Jan 2023 20:03:24 GMT

Reviewing this now. I may take overnight to digest, @PaigeMiller. It's been an interesting Thursday thus far.

Re: Ordering dates pursuant to proc transpose function

hnb_matt_d — Thu, 26 Jan 2023 20:13:39 GMT

This really looks like the same thing that proc tabulate was doing (which seemed to work correctly), but I need the Output Data formatted and not just the Results tab to be correct.

Re: Ordering dates pursuant to proc transpose function

hnb_matt_d — Thu, 26 Jan 2023 20:29:21 GMT

The proc report example provided also doesn't work with the date formatting needed. These transaction code volumes are being aggregated by month. Having a date of 1/1/2022 appearing to represent January 2022 won't work. The inconsistency in the presentation will be noticed and called out. This, unfortunately, is a compliance-based document and has to adhere to established guidelines. Not my call on that.

Re: Ordering dates pursuant to proc transpose function

PaigeMiller — Thu, 26 Jan 2023 20:44:08 GMT

but I need the Output Data formatted and not just the Results tab to be correct.

I don't understand what this means, but there are a gazillion and 12 different formatting options in PROC REPORT.

Re: Ordering dates pursuant to proc transpose function

PaigeMiller — Thu, 26 Jan 2023 20:46:00 GMT

@hnb_matt_d wrote:

These transaction code volumes are being aggregated by month. Having a date of 1/1/2022 appearing to represent January 2022 won't work.

All you have to do is change the format.

Re: Ordering dates pursuant to proc transpose function

ballardw — Thu, 26 Jan 2023 20:50:12 GMT

@hnb_matt_d wrote:

Can't really share the specific transaction_type _code. I can tell you there are ~400 of them with ~2.5 billion occurrences across those 400 in the last 13 months.

Also, this will be an automated code execution and output, so I'd rather not hard-code dates/years. Need this to be as self-sustainingly dynamic as possible.

Have you verified that each transaction code has a record in each month?

I suspect you may be leaving out some details. If you have duplicate month values for any given transaction code using the shown code would fail with an error similar to this:

ERROR: The ID value "DEC22" occurs twice in the same BY group.

Which means that the maximum number of records you could be dealing with is 400 codes * 13 months and expect the transpose to work which is WAY short of 2.5 billion.

Re: Ordering dates pursuant to proc transpose function

Reeza — Thu, 26 Jan 2023 20:59:25 GMT

Building on @SASJedi solution a brute force method, that's not ideal is to select the columns in the date order from a list of macro variables. There's a few ways of doing this but it's data driven. Basically adds two more steps to your code. See the last two steps in the code below. Note because it's data driven, if you change from 7 to 12 months, you could either just select a smaller amount by modifying the SQL query or if the data changes to just 7 months it would be data driven so you have some options to make it dynamic.

/*Make fake data*/
data volumes;
	call streaminit(12345);
	do transaction_type_code = "CHECK","CASH","CARD";
		DO YEAR=2021 TO 2022;
			do Month=1 TO 12;
				month_2 = input(CATS(YEAR,PUT(month,Z2.),"01"),yymmdd8.);
				Volume=rand('INTEGER',10,1000);
				IF month_2 >= '01DEC2021'd then output;
			end;
		end;
	end;
format month_2 monyy.; 
run;

proc sort data=volumes (drop=month year);
	by transaction_type_code month_2;
run;

proc transpose data=volumes out=PIVOT (drop=_:);
   by transaction_type_code;
   id month_2; 
   var volume;
run;

proc sql noprint;
select distinct month_2 into :date_list separated by " ," from volumes
order by month_2;
quit;

proc sql;
create table volumes_report as 
select transaction_type_code, &date_list
from pivot;
quit;