Getting correct count of counties by organization

[wlierman]

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

[PaigeMiller · Accepted solution]

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

or

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

[Reeza]

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

---

[ballardw]

---

@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.

[wlierman]

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