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
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;
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;
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
@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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.