topic Re: creating a new dataset where duplicate rows in the old set are attached as new columns in SAS Enterprise Guide

creating a new dataset where duplicate rows in the old set are attached as new columns

Rickyboy — Tue, 21 Nov 2017 17:10:25 GMT

Hi Guys

it s my first post here

I m a newbie and I m stuck on a problem

I need to reshape a dataset so that duplicate rows in the old are moved to a single row with twice the column lengths in the new dataset

for example

A B C

A D E

F G H

F I J becomes A B C A D E

F G H F I J

how would I go about it ?

even better is there a way to do this in enterprise guide ?

Thanks for your help

Re: creating a new dataset where duplicate rows in the old set are attached as new column

Shmuel — Tue, 21 Nov 2017 17:17:33 GMT

Is the first variable the ID - the indication up to which observation to gathr the data ?

If yes - why to save it twice on the row ?

What would you do if there are 3 or 4 or more observations for the same ID?

In many cases, sas can deal better and easier with long form, as you have,

than with the wide form that you want.

Re: creating a new dataset where duplicate rows in the old set are attached as new columns

Ksharp — Wed, 22 Nov 2017 13:03:33 GMT

It is easy for IML code.

data have;
input x1 $ x2 $ x3 $ ;
cards;
A B C
A D E 
F G H
F I J 
;
run;
proc iml;
use have;
read all var _char_ into x;
close;
want=cshape(x,2,0,1);
print want;
quit;

Re: creating a new dataset where duplicate rows in the old set are attached as new columns

Rickyboy — Thu, 23 Nov 2017 08:48:43 GMT

Thanks on that case I do I create a computed column that takes the
difference between two rows providing the label in the first column matches.
something like
If A1=a2 then c1-c2 else 0
I have found it difficult to perform operations between rows so I thought
expanding will make it easier but I m new sas

Re: creating a new dataset where duplicate rows in the old set are attached as new columns

Ksharp — Thu, 23 Nov 2017 12:04:03 GMT

Post some sample data and output that would be better to explain your question.

If you are doing PROC REPORT, I am afraid that you can't do it with PROC REPORT.

Re: creating a new dataset where duplicate rows in the old set are attached as new columns

Rickyboy — Fri, 24 Nov 2017 10:00:42 GMT

The data I have is something like this

USERID INCOME DATE

Rick 12.000 march 2015

Rick 13.000 September 2016

Mario 15.000 February 2013

Mario 16.000 June 2015

Mario 17.000 August 2017

Frank 20.000 June 2015

Frank 25.000 March 2016

I would like to compute a column that takes 13.000 - 12.000 = 1000 for rick and tells me if possible the amount of time it passed

same for Mario 1000 and 1000 (or 2000) and Frank 5000 in 8 months

this is what I m trying to do

that s why I tried to spread the data to add more column

this is quite easy in excel but I m stuck on SAS

thanks for your help

Re: creating a new dataset where duplicate rows in the old set are attached as new columns

Shmuel — Fri, 24 Nov 2017 10:35:25 GMT

Assuming the data is sorted by USERID and date is a sas numeric date

then the next code may do what you want:

data want;
 set have;
  by userid;
       retain start_income start_date;
       if first.userid then do;
          start_income = income;
          start_date = date.
       end;
      
      if last.userid then do;
         delta_income = income = start_income;
         delta_date = intck('month', date, start_date);
          output;
      end;
      keep userid delta_income delta_date;
run;