turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- proc transpose - incomplete results?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-10-2015 06:56 PM

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

Accepted Solutions

Solution

08-11-2015
03:17 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 03:17 AM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-10-2015 07:22 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 03:17 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 10:00 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 10:05 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 11:02 AM

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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 11:15 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 11:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 11:17 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-11-2015 11:26 AM

This works too. Thanks!