SAS Proc SQL Count Issue

Question

I have one column of data and the column is named (Daily_Mileage). I have 15 different types of daily mileages and 250 rows. I want a separate count for each of the 15 daily mileages. I am using PROC SQL in SAS and it does not like the Cross join command. I am not really sure what I should do but this is what I started:

PROC SQL;
select A, B
From (select count(Daily_Mileage) as A from Work.full where Daily_Mileage = 'Farm Utility Vehicle (Class 7)') a
cross join (select count(Daily_Mileage) as B from Work.full where Daily_Mileage = 'Farm Truck Light (Class 35)') b);
QUIT;

score 2 · Answer 1 · answered Aug 21 '14 at 14:34

Use case statements to define your counts as below.

proc sql;
  create table submit as
  select sum(case when Daily_Mileage = 'Farm Utility Vehicle (Class 7)'
                  then 1 else 0 end) as A,
         sum(case when Daily_Mileage = 'Farm Truck Light (Class 35)'
                  then 1 else 0 end) as B
  from Work.full
  ;
quit ;

score 1 · Answer 2 · answered Aug 21 '14 at 14:59

Can't you just use a proc freq?

data example ;
  input @1 Daily_Mileages $5. ;
datalines ;
TYPE1
TYPE1
TYPE2
TYPE3
TYPE3
TYPE3
TYPE3
;
run ;

proc freq data = example ;
  table Daily_Mileages ;
run ;

/* Create an output dataset */    
proc freq data = example ;
  table Daily_Mileages /out=f_example ;
run ;

score 0 · Answer 3 · answered Aug 01 '13 at 14:42

You can first create another column of ones, then SUM that column and GROUP BY Daily_Mileage. Let me know if I'm misunderstanding your questions.

PROC SQL;
   CREATE TABLE tab1 AS
   SELECT Daily_Mileage, 1 AS Count, SUM(Count) AS Sum
   FROM <Whatever table your data is in>
       GROUP BY Daily_Mileage;
QUIT;

SAS Proc SQL Count Issue

3 Answers3