BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
iced_tea
Obsidian | Level 7

Hello Community,

Wondering if this is doable in SAS. I have a table like this:

Customer

Rental_Date

MovieTitle

12345

10-Aug-14

Pitch Perfect

12345

8-Sep-14

Yesterday

12345

17-Oct-14

Hunger Games

12345

29-Nov-14

Booksmart

12345

31-Dec-14

iRobot

67890

10-Jan-15

Gone Girl

67890

10-Jan-15

Titanic

67890

8-Apr-15

The Imitation Game

… where there are many rows per 1 customer. The table contains the dates and the movies the customer rented on those dates.

I eventually want to create a table that is 1 row per 1 patient, capturing only the earliest of their movie rentals, and dropping the rest.

To that end, I want to add 2 new columns, to make it look like this:

Customer

Rental_Date

MovieTitle

FirstVisit_Date

Class

12345

10-Aug-14

Pitch Perfect

10-Aug-14

Pitch Perfect

12345

8-Sep-14

Yesterday

 

 

12345

17-Oct-14

Hunger Games

 

 

12345

29-Nov-14

Booksmart

 

 

12345

31-Dec-14

iRobot

 

 

67890

10-Jan-15

Gone Girl

10-Jan-15

Combo

67890

10-Jan-15

Titanic

 

 

67890

8-Apr-15

The Imitation Game

 

 

Where “FirstVisit_Date” is recording the first date each customer came into the rental store to rent a movie and “Class” field is recording the name of the movie they rented. However, in cases when the customer rented more than 1 movie on their FirstVisit_Date, I want the “Class” field to say “Combo”. Using Customer 67890 as an example, I want the value in “Class” for this customer to say “Combo”, instead of “Gone Girl”.

How can I approach this issue?

 

I’ve written this code below, but it’s missing that component of assigning the value to “Combo”.

data LIB.Final_Movie_Rentals;                       
  set LIB.Movie_Rentals;     
  by Customer;
  if FIRST.Customer then do;
         FirstVisit_Date = Rental_Date; Class=MovieTitle;      
  end;
run;

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data have;
input Customer	Rental_Date :date9.	MovieTitle $25.;
format Rental_Date date9.;
cards;
12345	10-Aug-14	Pitch Perfect
12345	8-Sep-14	Yesterday
12345	17-Oct-14	Hunger Games
12345	29-Nov-14	Booksmart
12345	31-Dec-14	iRobot
67890	10-Jan-15	Gone Girl
67890	10-Jan-15	Titanic
67890	8-Apr-15	The Imitation Game
;

data want;
 set have;
 by customer rental_date;
 length class $15;
 if first.customer then if first.Rental_Date and last.Rental_Date then Class=MovieTitle;
 else if first.Rental_Date then class='Combo';
run;

View solution in original post

1 REPLY 1
novinosrin
Tourmaline | Level 20
data have;
input Customer	Rental_Date :date9.	MovieTitle $25.;
format Rental_Date date9.;
cards;
12345	10-Aug-14	Pitch Perfect
12345	8-Sep-14	Yesterday
12345	17-Oct-14	Hunger Games
12345	29-Nov-14	Booksmart
12345	31-Dec-14	iRobot
67890	10-Jan-15	Gone Girl
67890	10-Jan-15	Titanic
67890	8-Apr-15	The Imitation Game
;

data want;
 set have;
 by customer rental_date;
 length class $15;
 if first.customer then if first.Rental_Date and last.Rental_Date then Class=MovieTitle;
 else if first.Rental_Date then class='Combo';
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 491 views
  • 3 likes
  • 2 in conversation