BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BTAinRVA
Quartz | Level 8

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


1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1023 views
  • 0 likes
  • 3 in conversation