proc transpose - incomplete results?

[stlimpbizkit]

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

[ballardw]

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.

[arodriguez · Accepted solution]

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;

[stlimpbizkit]

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?

[arodriguez]

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);

[stlimpbizkit]

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:

[arodriguez]

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

[stlimpbizkit]

Beautiful! I learned quite a few tricks from this. Many thanks.

[Reeza]

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;

[stlimpbizkit]

This works too. Thanks!