Multiple dates per record- ARRAY

Reply
New Contributor
Posts: 3

Multiple dates per record- ARRAY

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;

Super User
Posts: 19,875

Re: Multiple dates per record- ARRAY

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

I'm having a hard time following this.

New Contributor
Posts: 3

Re: Multiple dates per record- ARRAY

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

Super Contributor
Posts: 644

Re: Multiple dates per record- ARRAY

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 DateSmiley Happy

                    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

New Contributor
Posts: 3

Re: Multiple dates per record- ARRAY

Posted in reply to RichardinOz

Thanks Richard.

e.

Ask a Question
Discussion stats
  • 4 replies
  • 230 views
  • 0 likes
  • 3 in conversation