topic Re: Getting correct count of counties by organization in SAS Programming

Getting correct count of counties by organization

wlierman — Thu, 10 Dec 2020 23:49:02 GMT

Hello,

I have a small data set that is set up like

Organization Counties_served Contact_tracing

Community Health Plans Gilliam Y

Baker Y

Lake Y

Multnomah Community Clinic Blaine Y

Callum Y

Hood River Y

Malheur Y

Habitat for Humanity Lane N

Meals on Wheels Lincoln Y

Columbia Y

Farm Workers Cooperative Malheur Y

Yamhill N

Washington N

Camas Y

Harney Y

My question is how to get a count of counties_served for each organization when the organization isn't repeated for each line.

I was planning on a Proc sql code and then use Order By to keep the Counties_served by Organization.

I am not sure on that approach. Your help is appreciated.

wklierman

Re: Getting correct count of counties by organization

PaigeMiller — Fri, 11 Dec 2020 00:02:29 GMT

data want;
    set have;
    if not missing(organization) then count=0;
    count+1;
run;

data have2;
    set have;
    length organization1 $ 36;
    retain organization1 $;
    if not missing organization then organization1=organization;
run;
proc freq data=have2;
    table organzation1;
run;

Re: Getting correct count of counties by organization

Reeza — Thu, 10 Dec 2020 23:59:24 GMT

I would suggest filling in the missing values. It will likely come in useful for future analysis and then you can use standard summarization and reporting procedures. Otherwise you're always trying to work around the issue.

Something like the following to fill in the missing values.

data filled;
set have (rename=organization = org);
retain organization;
if not missing(org) then organization = org;
drop org;
run;

@wlierman wrote:

Hello,

I have a small data set that is set up like

      Organization                  Counties_served       Contact_tracing

Community Health Plans                 Gilliam                           Y

                                                         Baker                            Y

                                                         Lake                             Y

Multnomah Community Clinic          Blaine                           Y

                                                         Callum                         Y

                                                         Hood River                  Y

                                                         Malheur                       Y

Habitat for Humanity                        Lane                            N

Meals on Wheels                             Lincoln                         Y

                                                        Columbia                      Y

Farm Workers Cooperative             Malheur                        Y

                                                        Yamhill                         N

                                                        Washington        N

                                                        Camas                         Y

                                                        Harney                         Y

My question is how to get a count of counties_served for each organization when the organization isn't repeated for each line.

I was planning on a Proc sql code and then use Order By to keep the Counties_served by Organization.

I am not sure on that approach. Your help is appreciated.

wklierman

Re: Getting correct count of counties by organization

ballardw — Fri, 11 Dec 2020 15:18:46 GMT

@wlierman wrote:

Hello,

I have a small data set that is set up like

      Organization                  Counties_served       Contact_tracing

Community Health Plans                 Gilliam                           Y

                                                         Baker                            Y

                                                         Lake                             Y

Multnomah Community Clinic          Blaine                           Y

                                                         Callum                         Y

                                                         Hood River                  Y

                                                         Malheur                       Y

Habitat for Humanity                        Lane                            N

Meals on Wheels                             Lincoln                         Y

                                                        Columbia                      Y

Farm Workers Cooperative             Malheur                        Y

                                                        Yamhill                         N

                                                        Washington        N

                                                        Camas                         Y

                                                        Harney                         Y

My question is how to get a count of counties_served for each organization when the organization isn't repeated for each line.

I was planning on a Proc sql code and then use Order By to keep the Counties_served by Organization.

I am not sure on that approach. Your help is appreciated.

wklierman

I would strongly suggest to make sure that your data is not missing critical values, like your Organization. It adds so much complexity if you have to try to code around the deficiency that you will spend way more time working around the holes than it would take to fix the data in the first place.

This is an example of code that would add a new variable with the organization for each record.

data fixedorg;
    set have;
    length neworg $ 50;
    retain neworg ;
    if not missing(organization) then neworg=organization;
 run;

Now any SQL, Proc Freq, Report, Tabulate will count things as needed.

Re: Getting correct count of counties by organization

wlierman — Tue, 15 Dec 2020 17:26:53 GMT

Thank you for your reply. That fits into the solution approach. I have used it and it performs just right.

I really appreciate your help and assistance.

Stay safe.

wlierman