help with PROC SQL

[marleeakerson]

Hello, 

I need some helping making a table. Taking the sample data:

CountyCode      Code        Score   

1                         1                2

1                         1                2

2                         1                2

2                         2                3

I want to create a table that looks something like this 

CountyCode     County_Cnt       Code_1   Code_2    Score_2     Score_3

 1                              2                     2              0             2               0

 2                              2                     1              1              1              1

So I want a summary table based on the count of observations in each county (countycode) and the sum of the different codes and scores within that county. I think the easiest way to do this would be in proc SQL but I am not sure. Is there an easy way to do this (assuming there are multiple codes and multiple scores for each countycode?

Thank you!

[ballardw]

Do you want a report that people read or an awkward to work with result actual data set?

A report could go something like:

proc tabulate data=have;
   class countycode code score;
   table countycode,
           n='County count'   (code score)*n=' '
          / misstext='0'
   ;
run;

Your want shows more of a count for Code and Score than "sum".

If you really mean a sum then provide a bit more data and the result that shows sums.

[Ksharp]

data have;
input CountyCode      Code        Score   ;
cards;
1                         1                2
1                         1                2
2                         1                2
2                         2                3
;

proc sql;
create table want as
select CountyCode,count(*) as County_Cnt,
 sum(code=1) as code1,sum(code=2) as code2,
 sum(score=2) as score1,sum(score=3) as score2
 from have
  group by CountyCode;
quit;