Hi there,
Below array I have used put the multiple records in one row by using date (take if first date and keep the rest dates in line next to the first date). But when I check the records if the first date carries the related records of first date, I am seeing problem (it is taking the late dates records into it, you can visually see it below). Is there any way to move the dates upper (to the first column) so that I will have rent, color , location info that belongs to the first date? Please see my array code below:
Many Thanks in advance.
Best,
Emel
****** HOW IT LOOKS LIKE****
ID date 1 date 2 date 3 rent, color, location
1 1/1/2011
1 1/1/2011
1 1/1/2011 2/1/2012 5/1/2014
**USED ARRAY CODE***;
data V9;
set V8;
by ID DATE;
retain index 0 ic1-ic3 dt1-dt3;
format dt1-dt3 mmddyy10.;
array ic(3) ic1-ic3;
array dt(3) dt1-dt3;
if first.ID then
do;
index=0;
do i=1 to 3;
ic(i)=.;
dt(i)=.;
end;
end;
index=index+1;
ic(index)=ID;
dt(index)=DATE;
output;
drop i index;
run;
data V10;
set V9;
by ID DATE;
if last.ID;
drop ID DATE;
run;
Can you post what your data looks like vs what you want it to look like?
I'm having a hard time following this.
Reeza,
Each customer has more than 1 car bought through out the years (each car has a SELL DATE) and comes with certain qualities. I want to pick the first car (with the qualities) but want to keep the other cars date in line (in the same raw). For other cars, I just need the date, for first car, I need all the qualities.
Please let me know if it is still unclear. DATE in code is the SELL date and ID is the customer ID, which is a unique identification.
Emel
You can probably do this in a single datastep using multiple set statements but a 2 step process using Transpose might be easier to follow
/* First get dates */
Proc Transpose data = V8
out = pivot
prefix = Date
;
By Id ;
Var Date ;
Run ;
/* Then merge with remaining data for first row */
data v10 ;
merge pivot (Keep = ID Date:)
V8 (Drop = Date)
;
By Id ;
If First.ID ;
Run ;
[untested code - might have to fiddle with the transposed date names]
Richard
Message was edited by: Richard Carson
Thanks Richard.
e.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.