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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 943 views
  • 3 likes
  • 2 in conversation