topic Re: How to compute a new column based on values in another column in SAS Enterprise Guide

How to compute a new column based on values in another column

goodyucee — Fri, 24 Jul 2020 20:04:11 GMT

I have a dataset of >2000 hospitals that contains the two variables (columns)

1. HRR Code (numeric code for hospital referral region that the hospital belongs to). There are 306 distinct HRR codes

2. BDTOT (Number of beds in each hospital)

I want to compute a new column that will show the number of beds in all hospitals in a particular HRR. Is there a conditional expression (possibly with a wildcard) that I can use to sum all the hospital beds for each instance of the 306 HRRs?

Thanks for helping out.

Re: How to compute a new column based on values in another column

mklangley — Fri, 24 Jul 2020 20:16:34 GMT

If you're just looking for the sum of BDTOT for each HRR_Code, then this should do it:

proc sql;
    create table want as
    select  HRR_Code, sum(BDTOT) as total_beds_by_hrr format comma8.
    from have
    group by HRR_Code;
quit;

Re: How to compute a new column based on values in another column

goodyucee — Fri, 24 Jul 2020 20:19:46 GMT

Thank you @mklangley. I am using Enterprise Guide here and I am a complete novice with programming.

Can you describe how I can compute the new column using the graphical interface of EG or the conditional expression that I have to type.

Re: How to compute a new column based on values in another column

mklangley — Fri, 24 Jul 2020 21:08:15 GMT

Here's how to do it using the Query Builder.

1. Locate your dataset. (Mine demo dataset is called MY_DATA, located in the WORK library).

2. Double-click on the dataset and then select Query Builder

3. Drag both columns (from the left) into the Select Data area in the middle.

4. Click the dropdown for BDTOT under Summary, and click SUM.

5. Click Run.

Using my data, here are the results I see:

Re: How to compute a new column based on values in another column

goodyucee — Fri, 24 Jul 2020 21:25:18 GMT

Thanks so much @mklangley.