topic Re: Create a new variable based on the number of entries in SAS Programming

Create a new variable based on the number of entries

jmariee — Fri, 15 Feb 2019 17:23:06 GMT

I want to create a new variable based on the number of entries.

This is a sample of my dataset.

HospitalName City State CountyName

Name 1 Columbia Tx Shelby

I would want a new variable to see how many hospitals are in each county specific to their state.

How would I be able to do this?

Re: Create a new variable based on the number of entries

VDD — Fri, 15 Feb 2019 17:36:00 GMT

Proc freg data=have out=want;

by state county;

quit;

http://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#freq_toc.htm

then you can merge the results from want back to have.

Re: Create a new variable based on the number of entries

Cynthia_sas — Fri, 15 Feb 2019 17:53:10 GMT

Hi:
This sounds like a simple PROC FREQ type of output. Do you need a data set or do you need a report?
I am guessing that your data set does not have just one row. So if you have a Texas County with 3 hospitals, would you expect to see a 3 in the new variable for each of the hospitals?

It's not clear to me what you would want to see populated in your new variable. In the fake data, below, Shelby county has 3 hospitals, Anderson county has 4 hospitals, Bexar county has 2 and there there are different hospitals in Ohio -- it sounds to me like you want a new data set, with a count variable. Perhaps something like this:

The report on the right side shows output from PROC FREQ with the LIST option. Otherwise, I'm not sure how your original data would get the new variable populated on every row.

Cynthia

Re: Create a new variable based on the number of entries

jmariee — Fri, 15 Feb 2019 17:52:54 GMT

Yes! I want a new data set with a count of how many hospitals there are in each county. Also for it to be categorized by state since some states have the same county names.

Re: Create a new variable based on the number of entries

Cynthia_sas — Fri, 15 Feb 2019 17:59:54 GMT

If you want a new data set, then you'd just need to create an output data set from PROC FREQ. I used the LIST option, so if your variables were named STATE and COUNTY as shown in my example, then:

and WORK.FRLIST would be the output data set.
Cynthia