BookmarkSubscribeRSS Feed
statkoc1
Calcite | Level 5

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;

4 REPLIES 4
Reeza
Super User

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

I'm having a hard time following this.

statkoc1
Calcite | Level 5

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

RichardinOz
Quartz | Level 8

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

statkoc1
Calcite | Level 5

Thanks Richard.

e.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 450 views
  • 0 likes
  • 3 in conversation