topic Re: Query on is there any way to reduce the size of output for bigger dataset? in SAS Programming

Query on is there any way to reduce the size of output for bigger dataset?

Amali6 — Fri, 15 May 2020 23:58:21 GMT

Hi everyone,

I tried proc report for calculating the total no of repeated guest to a specific hotel in my dataset which has 119390 observations in total. I got a longer output, is there any way to reduce the size of the output rather than scrolling the page too much down to see the result? Could you provide solution for this please?

Here i would like to show only the total 2032 using the same proc report, is there any way to do this?

proc report data=hotel.Hotel_bookings;
 column hotel is_repeated_guest;
 where hotel='City Hotel' and 
       is_repeated_guest=1;
 rbreak after/summarize style=[font_weight=bold];
 title'No of repeated guest to City Hotel';
 run;

Re: Query on is there any way to reduce the size of output for bigger dataset?

ballardw — Sat, 16 May 2020 00:54:01 GMT

Input data is often helpful.

If you want to count (or sum ) within a specific value then you would use a Define statement to tell the Proc that is what you want:

proc report data=hotel.Hotel_bookings;
 column hotel is_repeated_guest;
 where hotel='City Hotel' and 
       is_repeated_guest=1;
 define hotel / group;
 rbreak after/summarize style=[font_weight=bold];
 title'No of repeated guest to City Hotel';
 run;

Since your where statement will only have one value of hotel there really is no reason for an "rbreak after".
If you are running this without the where I would suggest not including the Rbreak and see what the result looks like.

Or quite possibly you might want to summarize your data and use proc print.

Re: Query on is there any way to reduce the size of output for bigger dataset?

Kurt_Bremser — Sat, 16 May 2020 06:38:21 GMT

A simple SQL will do it:

proc sql;
select count(*) as count
from hotel.hotel_bookings
where hotel = 'City Hotel' and is_repeated_guest = 1
;
quit;

Re: Query on is there any way to reduce the size of output for bigger dataset?

ed_sas_member — Sat, 16 May 2020 08:15:31 GMT

Hi @Amali6

What about using a PROC FREQ:

proc freq data=hotel_bookings;
	where hotel = 'City Hotel' and is_repeated_guest = 1;
	tables is_repeated_guest / nocum nopercent;
run;

Re: Query on is there any way to reduce the size of output for bigger dataset?

RichardDeVen — Sat, 16 May 2020 14:56:51 GMT

A Proc REPORT variable DEFINED with feature / GROUP will cause summary statistics to be computed. The default statistic is SUM, perfect for 'counting' a 0/1 flag variable.

Example:

data have_apriori_summary(keep=hotel_id guest_id is_repeated_guest);
  call streaminit (123);
  do hotel_id = 1 to 10;
    do index = 1 to 100;
      guest_id + 1;
      is_repeated_guest = rand ('uniform') < 0.40;
      output;
    end;
  end;
run;

proc report data=have_apriori_summary;
  title "Guest retention results from apriori per guest summary";
  column hotel_id is_repeated_guest;

  define hotel_id / group;
  define is_repeated_guest / 'Repeated Guests';

  where hotel_id in (1,3,4);
run;

Output

Consider a slightly different scenario:

Suppose the data set contains transactional information, such as CHECK_IN_DATE, ROOM_ID, and GUEST_ID and the REPEAT flag is a marker for some customer service action at the desk. Taking a sum of the flag values over the transactions will NOT give the number of DISTINCT guests, the SUM will be the number of repeated guest visits. You will need to reduce the transaction data to a summary in order to count the number of distinct guests.

Example:

data have_checkin_log(keep=hotel_id room_id date guest_id is_repeated_guest);
  call streaminit (123);
  array tracker(7500) _temporary_;
  do index = 1 to dim(tracker);
    tracker(index) = rand('uniform') < 0.15;
  end;
  do hotel_id = 1 to 10;
    do room_id = 1 to 50;
      do date = '01jan2020'd to '31jan2020'd;
        guest_id = rand('integer',1,7500);
        is_repeated_guest = tracker(guest_id);
        output;
      end;
    end;
  end;
  format date yymmdd10.;
run;

proc sql;
  create table repeated_guests_summary as 
  select 
    hotel_id,
    guest_id,
    is_repeated_guest,
    count(*) as repeated_visits_count
  from have_checkin_log
  where is_repeated_guest
  group by hotel_id, guest_id
  ;
quit;

proc report data=repeated_guests_summary;
  title "Repeated Guests and Visits";
  column hotel_id is_repeated_guest repeated_visits_count;

  define hotel_id / group;
  define is_repeated_guest / 'Repeated Guests';

  where hotel_id in (1,3,4);
run;

Output