Date:         Fri, 18 Apr 2008 20:37:11 +0300
Reply-To:     Mugu Gai Pan <mugu@PCN.NO>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mugu Gai Pan <mugu@PCN.NO>
Subject:      little help on Proc Sql and frequency
Comments: To: sas-l@uga.edu
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

say I have the following raw data set

data test; input vin_nbr $10. @12 code $2.;
datalines;
0911221122  j7
0911221122  j7
0911221122  j7
0911221122  j1
0311221122  j4
0311221122  j4
0311221122  j4
0311221122  j5
0311221122  j1
;
run;

my goal is to report total number of vin_nbr that match the following
conditions

1. total number of distinct vin_nbr
2. total number of vin_nbr which have a code "j1"

proc sql;
 create table result as
     select count(*) as total from(select distinct vin_nbr from test);
 create table result2 as
     select count(*) as total from(select distinct vin_nbr from test
                   where code in('j1'));
quit;
data _null_;set result  ;put(_all_)(=);run;
data _null_;set result2 ;put(_all_)(=);run;

how would I count total number of all codes within the total vin_nbr?

ie. j7 occurred 3 times but only within 1 vin_nbr, j1 2 times within 2
vin_nbr