11-21-2017 12:06 PM - edited 11-21-2017 12:10 PM
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
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
11-21-2017 12:17 PM
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.
11-22-2017 08:03 AM
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;
11-23-2017 03:48 AM
11-23-2017 07:04 AM
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.
11-24-2017 05:00 AM
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
11-24-2017 05:30 AM - edited 11-24-2017 05:35 AM
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;