topic Re: Count a categorical variable by group in SAS Programming

Count a categorical variable by group

DingDing — Sat, 24 Oct 2015 14:11:28 GMT

My dataset is like this

carrier

I want the output like this

B1 4

A9 2

OO 5

and then make a barplot, the following is my codes:

PROC MEANS DATA=schedule_Jan NOPRINT;
BY carrier ;
OUTPUT OUT= flight_count COUNT(carrier) =number_of_flights;
RUN;

PROC SGPLOT DATA= flight_count;
HBAR CARRIER;
RESPONSE = number_of_flights;
RUN;

but it stop at this line and the log is as below, could anybody give some explanation and solution aobut this ?

222  OUTPUT OUT= flight_count COUNT(unique_carrier) =number_of_flights;
                              -----
                              22
                              76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, /, CSS, CV, IDGROUP, IDGRP,
              KURTOSIS, LCLM, MAX, MAXID, MEAN, MEDIAN, MIN, MINID, MODE, N, NMISS, OUT, P1,
              P10, P20, P25, P30, P40, P5, P50, P60, P70, P75, P80, P90, P95, P99, PROBT, Q1,
              Q3, QRANGE, RANGE, SKEWNESS, STDDEV, STDERR, SUM, SUMWGT, T, UCLM, USS, VAR.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Count a categorical variable by group

kannand — Sat, 24 Oct 2015 14:44:43 GMT

data have;
input CARRIER $2.;
datalines;
B1
B1
B1
B1
A9
A9
OO
OO
OO
OO
OO
;RUN;
PROC SQL;
   SELECT DISTINCT CARRIER,COUNT(*) FROM HAVE GROUP BY CARRIER;
QUIT;

Re: Count a categorical variable by group

PaigeMiller — Sat, 24 Oct 2015 14:51:19 GMT

Use PROC FREQ instead of PROC MEANS

Re: Count a categorical variable by group

kannand — Sat, 24 Oct 2015 14:55:39 GMT

In addition, if you like to save it in a SAS dataset, you may create a Table within the PROC SQL as shown below...

PROC SQL PRINT;
   CREATE TABLE CARRIER_COUNT AS
          SELECT DISTINCT CARRIER,COUNT(*) AS FLIGHT_COUNT FROM HAVE GROUP BY CARRIER;
QUIT;

PROC PRINT DATA=CARRIER_COUNT NOOBS;
RUN;

Re: Count a categorical variable by group

DingDing — Sat, 24 Oct 2015 15:03:21 GMT

Thank you PaigeMiller, but if it is a column contains just several observations, "PROC FREQ" my work, yet the fact is that the variable "unique_carrier" in a dataset contarins many many different observations that I can not just use "table" to list all of them, so is there any solution?

I may make my example more clear, the carrier contains more observations not just the B1, A9 and OO that i enumerate.

carrier

.........

Re: Count a categorical variable by group

DingDing — Sat, 24 Oct 2015 15:08:03 GMT

Thank you kannand! I am sorry that I dont understand your code very much since I am just at the begining in using SAS, so could you add some explanation on the codes ? Thanks in advance.

Re: Count a categorical variable by group

kannand — Sat, 24 Oct 2015 15:39:28 GMT

/* 
========================================
This next step creates a SAS dataset of 
your data specified in the "datalines" 
========================================
*/

data have;
input CARRIER $2.;
datalines;
B1
B1
B1
B1
A9
A9
OO
OO
OO
OO
OO
;RUN;
/* 
========================================
This next step Uses "PROC SQL" to summarize 
your data specified in the "datalines and 
also saves the result in a SAS dataset 
called  CARRIER_SUMMARY" 
========================================
*/
PROC SQL PRINT;
   CREATE TABLE CARRIER_SUMMARY AS
          SELECT DISTINCT CARRIER,COUNT(*) AS FLIGHT_COUNT FROM HAVE GROUP BY CARRIER;
QUIT;
/*
========================================
This step just prints the summary created 
========================================
*/
PROC PRINT DATA=CARRIER_SUMMARY NOOBS;

Hope these added comments in the code help.

I've attached the SAS Log for your reference here:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 55         
 56         /*
 57         ========================================
 58         This next step creates a SAS dataset of
 59         your data specified in the "datalines"
 60         ========================================
 61         */
 62         
 63         data have;
 64         input CARRIER $2.;
 65         datalines;
 
 NOTE: The data set WORK.HAVE has 11 observations and 1 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.06 seconds
       cpu time            0.05 seconds
       
 77         ;RUN;
 
 78         /*
 79         ========================================
 80         This next step Uses "PROC SQL" to summarize
 81         your data specified in the "datalines and
 82         also saves the result in a SAS dataset
 83         called  CARRIER_SUMMARY"
 84         ========================================
 85         */
 86         PROC SQL PRINT;
 87            CREATE TABLE CARRIER_SUMMARY AS
 88                   SELECT DISTINCT CARRIER,COUNT(*) AS FLIGHT_COUNT FROM HAVE GROUP BY CARRIER;
 NOTE: Table WORK.CARRIER_SUMMARY created, with 3 rows and 2 columns.
 
 89         QUIT;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.04 seconds
       cpu time            0.04 seconds
       
 
 90         /*
 91         ========================================
 92         This step just prints the summary created
 93         ========================================
 94         */
 95         PROC PRINT DATA=CARRIER_SUMMARY NOOBS;
 96         
 97         
 98         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 110

The code produced the following output. Hope this helps...!!! Good Luck to you DingDing.

CARRIER	FLIGHT_COUNT
A9	2
B1	4
OO	5

Re: Count a categorical variable by group

DingDing — Sat, 24 Oct 2015 15:38:48 GMT

Thank you , kannand! I am inspired by your code and then change them as:

proc sql ;
create table flight_count as
select unique_carrier, count(*) as number_of_flights
from schedule_Jan
group by unique_carrier
order by unique_carrier
;
quit;

then it works!

really very appreciate for helping me !!!

Re: Count a categorical variable by group

PaigeMiller — Sun, 25 Oct 2015 01:04:40 GMT

@DingDing wrote:

Thank you PaigeMiller, but if it is a column contains just several observations, "PROC FREQ" my work, yet the fact is that the variable "unique_carrier" in a dataset contarins many many different observations that I can not just use "table" to list all of them, so is there any solution?

I may make my example more clear, the carrier contains more observations not just the B1, A9 and OO that i enumerate.

carrier

B1

B1

B1

B1

A9

A9

OO

OO

OO

OO

OO

.........

Sorry, but I don't think that makes anything clearer, and while I'm glad you seem to have found a solution, from my understanding of what you have said, PROC FREQ fits perfectly.