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.
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?
The decimal point is not allowed in variable names. You could add a label and use that during reporting.
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;
@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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.