Assigning a value to a new column

iced_tea — Fri, 09 Oct 2020 01:56:38 GMT

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!

Re: Assigning a value to a new column

novinosrin — Fri, 09 Oct 2020 02:37:53 GMT

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;

topic Re: Assigning a value to a new column in SAS Programming

Assigning a value to a new column

Re: Assigning a value to a new column