proc transpose - incomplete results?

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

proc transpose - incomplete results?

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.

SegmentRev1Rev2Rev12Inv1Inv2Inv12Exp1Exp2Exp12
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)
Seg1Rev1
Seg1Rev2
Seg1Rev3
Seg1Rev4
Seg1Rev5
Seg1Rev6
Seg1Rev7
Seg1Rev8
Seg1Rev9
Seg1Rev10
Seg1Rev11
Seg1Rev12
Seg1Exp1
Seg1Exp2
Seg1Exp3
Seg1Exp4
Seg1Exp5
Seg1Exp6
Seg1Exp7
Seg1Exp8
Seg1Exp9
Seg1Exp10
Seg1Exp11
Seg1Exp12
Seg2Rev1
Seg2Rev2
Seg2Rev3
Seg2Rev4
Seg2Rev5
Seg2Rev6
Seg2Rev7
Seg2Rev8
Seg2Rev9
Seg2Rev10
Seg2Rev11
Seg2Rev12
Seg2Exp1
Seg2Exp2
Seg2Exp3
Seg2Exp4
Seg2Exp5
Seg2Exp6
Seg2Exp7
Seg2Exp8
Seg2Exp9
Seg2Exp10
Seg2Exp11
Seg2Exp12

Many thanks!

Tao


Accepted Solutions
Solution
‎08-11-2015 03:17 AM
Frequent Contributor
Posts: 144

Re: proc transpose - incomplete results?

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;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: proc transpose - incomplete results?

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.

Solution
‎08-11-2015 03:17 AM
Frequent Contributor
Posts: 144

Re: proc transpose - incomplete results?

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;

Contributor
Posts: 23

Re: proc transpose - incomplete results?

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?

Frequent Contributor
Posts: 144

Re: proc transpose - incomplete results?

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

Contributor
Posts: 23

Re: proc transpose - incomplete results?

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:

Frequent Contributor
Posts: 144

Re: proc transpose - incomplete results?

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

Contributor
Posts: 23

Re: proc transpose - incomplete results?

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

Super User
Posts: 17,819

Re: proc transpose - incomplete results?

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;

Contributor
Posts: 23

Re: proc transpose - incomplete results?

This works too. Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 376 views
  • 9 likes
  • 4 in conversation