topic Re: Split column function - adds revenue? in SAS Enterprise Guide

Split column function - adds revenue?

VictorNilsson — Fri, 02 Jun 2017 08:14:10 GMT

Hi, I am a new user in SAS and i have encountered a problem that I need help to solve.

I have an issue when using the split column function in SAS EG.

I have a Data-set with the following example columns:

User_ID Segment Product Period Revenue

12345 Retail Shoes 2016-02 500

I would like to use the split function in order to get the period spltted into several new columns for each month of the year, instead rows. Somehow, when I perform the split column task, it adds about 20% to the total revenue. Before making the split I have used the sum function for the revenue.

Can someone please tell me what I am doing wrong here? 🙂

Re: Split column function - adds revenue?

RW9 — Fri, 02 Jun 2017 08:20:38 GMT

Is period a date variable? If so you wouldn't split it (which is for character strings) but rather extract the various parts using year() or month() functions. E.g.:

data want;
  set have;
  yr=year(period);
  mnth=month(period);
run;

Re: Split column function - adds revenue?

VictorNilsson — Fri, 02 Jun 2017 08:51:11 GMT

@RW9 Period is formated as charachter string and revenue in number format. 😕

First I assumed that it would be better to use the "Transpose function"? But as i understood it, the transpose function is preferably used in the opposite way, whenever you would like to transpose a column into a row? It feels like i'm swimming in the deep water over here 🙂 haha.

Re: Split column function - adds revenue?

RW9 — Fri, 02 Jun 2017 09:28:48 GMT

All I can think is that with the extra variables its grouping the data differently. Sorry, I don't use EG so can't be further help as I only use code. Transpose can be used either way, normalised to transposed and vice versa. If you have multiple variables or different types then arrays can be more effective.

Re: Split column function - adds revenue?

ballardw — Fri, 02 Jun 2017 14:36:23 GMT

Maybe you should be looking at REPORTING tasks where the variable Period has a grouping column role.