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
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;
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;
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;
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.