topic Re: Formatting transposed Date headers in SAS Programming

Formatting transposed Date headers

Q1983 — Fri, 23 Apr 2021 04:04:41 GMT

I received a data file similar to this code that needs to be transposed to show dates as headers

data have;
 length catgy $25
	month_ $5 
	Sales 8
	Return 8;
	input catgy -- return;
datalines;
Engineering JAN20 10 20 
Engineering JAN20 22 46 
Engineering FEB20 30 17  
Engineering FEB20 60 32   
Engineering MAR20 40 44 
Engineering APR20 60 44   
Engineering APR20 40 22
;
run;

proc transpose data=have out =have2 let;
id month_;
var sales return;
by catgy;
run;

Output from the proc transpose is this

catgy	_NAME_	JAN20	FEB20	MAR20	APR20
Engineering	Sales	22	60	40	40
Engineering	Return	46	32	44	22

The issue is I cannot format the date headers (which will be dynamic each month). As an example I want to format the headers with a number with 2 decimal points (ie 22 becomes 22.00) Is there a way I can handle this without hardcoding the date headers? Month_ originally is a single column that was transposed to horizontal format.

Re: Formatting transposed Date headers

Tom — Fri, 23 Apr 2021 04:58:53 GMT

Why do want to transpose the dataset? Is it for a report? If so use PROC REPORT.

If not how are you planning to use this new structure that couldn't be done more easily with the original structure?

Re: Formatting transposed Date headers

andreas_lds — Fri, 23 Apr 2021 05:39:03 GMT

The decimal point is not allowed in variable names. You could add a label and use that during reporting.

Re: Formatting transposed Date headers

Kurt_Bremser — Fri, 23 Apr 2021 09:06:56 GMT

First, date values need to be handled as such, not as strings that can't be used in calculations and do not sort correctly.

Second, always strive for a long dataset layout, as this can most easily be used in procedures without prior knowledge of the actual content.

data have;
length
  catgy $25
  month_ 4
  Sales 8
  Return 8
;
input catgy month_ :monyy5. sales return;
format month_ yymmd7.;
datalines;
Engineering JAN20 10 20 
Engineering JAN20 22 46 
Engineering FEB20 30 17  
Engineering FEB20 60 32   
Engineering MAR20 40 44 
Engineering APR20 60 44   
Engineering APR20 40 22
;

proc transpose
  data=have
  out=long (rename=(_name_=cat col1=value));
;
by catgy month_;
var sales return;
run;

proc report data=long;
column catgy cat value,month_;
define catgy / "Department" group;
define cat / "Category" group;
define value / "" analysis;
define month_ / "" across;
run;

Re: Formatting transposed Date headers

Q1983 — Fri, 23 Apr 2021 13:28:31 GMT

Thanks for the response. For your routine in proc report "define month_ / "" across is there a way to show returns as a percentage of sales? In this case the proc report would show sales as a numeric and returns as a percentage8.3

Also in your response were you saying that its best to use a long version of date yymmd7 as opposed to the monyy5

Re: Formatting transposed Date headers

ballardw — Fri, 23 Apr 2021 15:04:23 GMT

@Q1983 wrote:

Also in your response were you saying that its best to use a long version of date yymmd7 as opposed to the monyy5

"Long" meant the data set structure: one record per date instead of "wide" with multiple dates(hidden in variable names to an extent) per record.

You would have to share your formula for " returns as a percentage of sales". But Proc report does allow you to do calculations based on results of the statistics calculated.