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!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.