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

[Rickyboy]

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

[Shmuel]

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.

[Ksharp]

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;

[Rickyboy]

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

[Ksharp]

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.

[Rickyboy]

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

[Shmuel]

   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;