BookmarkSubscribeRSS Feed
marleeakerson
Calcite | Level 5

Hello, 

 

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

 

CountyCode      Code        Score   

                       1                2

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

2 REPLIES 2
ballardw
Super User

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
Super User
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 521 views
  • 1 like
  • 3 in conversation