DATA Step, Macro, Functions and more

Count a categorical variable by group

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

Count a categorical variable by group

My dataset is like this 

carrier 

B1

B1

B1

B1

A9

A9

OO

OO

OO

OO

OO

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.

 

 


Accepted Solutions
Solution
‎10-24-2015 11:56 AM
Regular Contributor
Posts: 161

Re: Count a categorical variable by group

[ Edited ]

 

/* 
========================================
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
Kannan Deivasigamani

View solution in original post


All Replies
Regular Contributor
Posts: 161

Re: Count a categorical variable by group

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;
Kannan Deivasigamani
Regular Contributor
Posts: 161

Re: Count a categorical variable by group

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;
Kannan Deivasigamani
Contributor
Posts: 48

Re: Count a categorical variable by group

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. 

Solution
‎10-24-2015 11:56 AM
Regular Contributor
Posts: 161

Re: Count a categorical variable by group

[ Edited ]

 

/* 
========================================
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
Kannan Deivasigamani
Contributor
Posts: 48

Re: Count a categorical variable by group

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 !!!

Trusted Advisor
Posts: 1,610

Re: Count a categorical variable by group

Use PROC FREQ instead of PROC MEANS

Contributor
Posts: 48

Re: Count a categorical variable by group

Thank you PaigeMiller, but 

 

B1, A9 and OO that i enumerate.

 

carrier 

B1

B1

B1

B1

A9

A9

OO

OO

OO

OO

OO

.........

Trusted Advisor
Posts: 1,610

Re: Count a categorical variable by group


DingDing wrote:

Thank you PaigeMiller, but 

 

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 509 views
  • 3 likes
  • 3 in conversation