topic Re: proc transpose - incomplete results? in SAS Programming

proc transpose - incomplete results?

stlimpbizkit — Mon, 10 Aug 2015 22:56:36 GMT

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

Re: proc transpose - incomplete results?

ballardw — Mon, 10 Aug 2015 23:22:47 GMT

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.

Re: proc transpose - incomplete results?

arodriguez — Tue, 11 Aug 2015 07:17:23 GMT

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;

Re: proc transpose - incomplete results?

stlimpbizkit — Tue, 11 Aug 2015 14:00:14 GMT

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?

Re: proc transpose - incomplete results?

arodriguez — Tue, 11 Aug 2015 14:05:01 GMT

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

Re: proc transpose - incomplete results?

stlimpbizkit — Tue, 11 Aug 2015 15:02:56 GMT

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:

Re: proc transpose - incomplete results?

arodriguez — Tue, 11 Aug 2015 15:15:27 GMT

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

Re: proc transpose - incomplete results?

Reeza — Tue, 11 Aug 2015 15:17:14 GMT

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;

Re: proc transpose - incomplete results?

stlimpbizkit — Tue, 11 Aug 2015 15:25:38 GMT

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

Re: proc transpose - incomplete results?

stlimpbizkit — Tue, 11 Aug 2015 15:26:41 GMT

This works too. Thanks!