topic help with PROC SQL in New SAS User

help with PROC SQL

marleeakerson — Tue, 24 Nov 2020 22:42:05 GMT

Hello,

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

CountyCode Code Score

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!

Re: help with PROC SQL

ballardw — Tue, 24 Nov 2020 23:19:25 GMT

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.

Re: help with PROC SQL

Ksharp — Wed, 25 Nov 2020 12:29:50 GMT

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;