Hello all - I ran into an obstacle when using proc transpose module:
The structure of input data is as follows - segment is the index / unique identifier, followed by a series of metrics including revenue, inventory, expense, etc. Each metric has 12 columns representing monthly values for a 1-year period. The goal is to rotate the dataset so that metric values are listed vertically for further data steps.
Segment | Rev1 | Rev2 | … | Rev12 | Inv1 | Inv2 | … | Inv12 | Exp1 | Exp2 | … | Exp12 |
Seg1 | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Seg2 | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Seg3 | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Seg10 | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Here is the code I used:
proc transpose data = input_data out = output_data;
by segment;
run;
The output is almost what I hoped for but missing one series of metric - inventory. Notice how the rows cover revenue and expense but not inventory metrics for each segment. It seems as if SAS skipped Inv for a very particular reason (which I can't figure out what). Anyone experienced something similar? I have tried with simplified input dataset in order to locate the root cause, however am getting the same incomplete results. Any thoughts would be greatly appreciated. Thanks in advance.
Segment | _NAME_ | COL1 (metric value) |
Seg1 | Rev1 | … |
Seg1 | Rev2 | … |
Seg1 | Rev3 | … |
Seg1 | Rev4 | … |
Seg1 | Rev5 | … |
Seg1 | Rev6 | … |
Seg1 | Rev7 | … |
Seg1 | Rev8 | … |
Seg1 | Rev9 | … |
Seg1 | Rev10 | … |
Seg1 | Rev11 | … |
Seg1 | Rev12 | … |
Seg1 | Exp1 | … |
Seg1 | Exp2 | … |
Seg1 | Exp3 | … |
Seg1 | Exp4 | … |
Seg1 | Exp5 | … |
Seg1 | Exp6 | … |
Seg1 | Exp7 | … |
Seg1 | Exp8 | … |
Seg1 | Exp9 | … |
Seg1 | Exp10 | … |
Seg1 | Exp11 | … |
Seg1 | Exp12 | … |
Seg2 | Rev1 | … |
Seg2 | Rev2 | … |
Seg2 | Rev3 | … |
Seg2 | Rev4 | … |
Seg2 | Rev5 | … |
Seg2 | Rev6 | … |
Seg2 | Rev7 | … |
Seg2 | Rev8 | … |
Seg2 | Rev9 | … |
Seg2 | Rev10 | … |
Seg2 | Rev11 | … |
Seg2 | Rev12 | … |
Seg2 | Exp1 | … |
Seg2 | Exp2 | … |
Seg2 | Exp3 | … |
Seg2 | Exp4 | … |
Seg2 | Exp5 | … |
Seg2 | Exp6 | … |
Seg2 | Exp7 | … |
Seg2 | Exp8 | … |
Seg2 | Exp9 | … |
Seg2 | Exp10 | … |
Seg2 | Exp11 | … |
Seg2 | Exp12 | … |
… | … | … |
Many thanks!
Tao
Hi,
If you use the VAR option to indicate wich variable do you like to transpose of the proc transpose should be solved.
proc transpose data = input_data out = output_data;
by segment;
var Rev1-Rev12 Inv1-Inv12 Exp1-Exp12;
run;
Mixture of numeric and character variables comes to mind as a likely issue.
Maybe a row of example data that exhibits the problem would help diagnose more specific issues.
Hi,
If you use the VAR option to indicate wich variable do you like to transpose of the proc transpose should be solved.
proc transpose data = input_data out = output_data;
by segment;
var Rev1-Rev12 Inv1-Inv12 Exp1-Exp12;
run;
Many thanks for the suggestion. It solved the incomplete result issue. For continuity, please allow me to expand the question slightly further beyond proc transpose:
What if the number of month is dynamic instead of constant? In the actual case, I defined a macro variable counting number of months that should be included:
%let m = 12*(year(end_date) - year(start_date)) + (month(end_date) - month(start_date)) + 1;
And then, I refer to variable m when calling for variables in proc transpose: var Rev1-Rev&m.
In this case, SAS will insert the whole macro logic after "Rev" which then would consider Rev as a undefined function, instead of Rev12 that I was going for.
How do I go around this?
I think that should work with var Rev1-Rev&m always that you apply the %eval function. Try something like this
var Rev1-Rev%eval(&m);
Hi arodriguez, many thanks for the advice. I am now one step closer, with one error message to solve for within %eval function:
"A character operand was found in the %EVAL function or %IF condition where a numeric operand is required."
Here's the code I used:
%let m = 12*(year(end_date) - year(start_date)) + (month(end_date) - month(start_date)) + 1;
proc transpose data =input_date out = output_date;
by segment;
var
rev1-rev%eval(&m)
inv1-inv%eval(&m)
exp1-exp%eval(&m)
run;
I also tried the following attempting to force &m into numeric format but no luck:
A. rev1-rev%eval(input(&x,4.0));
B. rev1-rev%eval(&x*1);
:smileyconfused::smileyconfused::smileyconfused:
:smileyconfused::smileyconfused::smileyconfused:
:smileyconfused::smileyconfused::smileyconfused:
Are you calculating "m" on a data step? Because in your calculation you don't have "&"
try to calculate it in a data step such this;
data test;
m = 12*(year(end_date) - year(start_date)) + (month(end_date) - month(start_date)) + 1;
call symputx('m',m);
run;
And check that "m" is well calculated. The symputx should transform the "m" value to macro value
Beautiful! I learned quite a few tricks from this. Many thanks.
If rev, exp and inv are the only variables that start with that prefix you can use the colon shortcut.
proc transpose data = input_data out = output_data;
by segment;
var Rev: Inv: Exp:;
run;
This works too. Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.