topic Re: Multiple dates per record- ARRAY in SAS Programming

Multiple dates per record- ARRAY

statkoc1 — Fri, 16 May 2014 15:08:16 GMT

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 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;

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;

Re: Multiple dates per record- ARRAY

Reeza — Fri, 16 May 2014 16:40:55 GMT

Can you post what your data looks like vs what you want it to look like?

I'm having a hard time following this.

Re: Multiple dates per record- ARRAY

statkoc1 — Fri, 16 May 2014 17:00:01 GMT

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

Re: Multiple dates per record- ARRAY

RichardinOz — Sat, 17 May 2014 05:22:47 GMT

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

Re: Multiple dates per record- ARRAY

statkoc1 — Mon, 19 May 2014 13:45:02 GMT

Thanks Richard.