Identify Frequent Flyers

[BTAinRVA]

I have a table with customer ID column and I want to create a new column that counts the occurences of the customer ID. So if the customer ID in the first row occurs 4 time within the ID column I want the number 4 to show up in the new column wherever that customer ID is in the ID column. Maybe this doesn't make sense and there is a better way to accomplish this. All replies are welcome!

Thanks, Brian

[Cynthia_sas · Accepted solution]

Hi:
This would be easier if you could show some data or some code. If you can't post your data, then making an example that uses SASHELP.CLASS or SASHELP.SHOES, would be useful. For example, in the code below, I used SASHELP.CLASS and made a "fake" customerID variable by adding a random number to the AGE variable.

You should be able to run the code and see the input data and the output report. I generated the report 2 different ways, one with PROC REPORT and the other way with PROC TABULATE. Unless you have different data complexities or different report needs, one of these examples should do what you describe.

Cynthia

data class;
  set sashelp.class;
  customerID = age+3200;
run;
 
ods _all_ close;
ods html file='c:\temp\countID.html';
proc print data=class;
  title 'what is input data';
  var customerID name height weight;
run;

proc report data=class nowd;
  title 'Proc Report Getting a Count Column';
  column customerID n ('Average' height weight);
  define customerID / group
         style(column)=Header;
  define n / 'Count';
  define height / mean f=6.2;
  define weight / mean f=6.2;
run;

proc tabulate data=class;
  var height weight;
  class customerID;
  table customerID,
         n  mean='Average'*(height weight);
  title 'TABULATE Getting a Count';
  keylabel n='Count';
  keyword n / style={vjust=b};
run;

ods html close;

[Reeza]

This involves calculating the number and merging in, though you can do it in one step via SQL. It still requires two passes, and you'll see a note in the log that indicates this:

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: Table WORK.CARS_COUNT created, with 428 rows and 16 columns.

proc sql;

     create table cars_count as

select *, count(m) as count_of_make

from sashelp.cars

group by make;

quit;