CODEWITHSUNDEEP
datesas
Paul
Paul

Reputation: 33

SAS: Date intervals into columns

I want to do something in SAS that I am struggling to put into words.

Basically, I have a list of hotels with dates of stays - see table one. I want to change the table so that I have a list of nights stayed - see table two.

My first table has two fields reflecting when a customer arrives and second field reflecting when a customer leaves a hotel. The second table the date fields lists the nights stayed at the hotel. I say nights as the second date is the date they leave the hotel so they wont stay the night.

How do I get from table one to table two?

Any suggestions would be welcome.

Thanks in advance.

Paul

enter image description here

Upvotes: 1

Views: 85

Answers (2)

andrey_sz
andrey_sz

Reputation: 751

To calc count of nights you should to this code^

data table_one;
   set table_one;
   count_nights = out_date - in_date;
run;

To add this calculated attribute to table_two try this code:

Ex. 1:

data table_two;
   merge table_two table_one;
   by hotel, customer_name, city;
run;

Ex. 2:

Also you can calc count of nigths from table_two as

proc sql;
   create table table_two_nights as
   select t.*, count(*) as count_nights
   from table_two
   group by hotel, customer_name, city;
quit;

Upvotes: -1

Longfish
Longfish

Reputation: 7602

Since dates are stored as numbers, it's a pretty straightforward task to just loop from the in_date to the day before the out_date and output each iteration of the loop.

data have;
infile datalines dsd;
input hotel $ customer_name $ City $ in_date :date9. out_date :date9.;
format in_date out_date date9.;
datalines;
Hotel A, Joe, New York, 01jan2015, 04jan2015
Hotel B, Joe, Chicago, 05feb2015, 07feb2015
Hotel C, Sam, Portland, 15jun2015, 18jun2015
Hotel D, Amy, Boston, 04mar2015, 07mar2015
Hotel C, Sam, Portland, 15oct2015, 17oct2015
Hotel A, Tom, New York, 01nov2015, 02nov2015
;
run;

data want;
set have;
format stay_date date9.;
do stay_date = in_date to out_date-1;
output;
end;
drop in_date out_date;
run;

Upvotes: 2

Related Questions