Create a macro variable with count of diagnosis in SQL

[YGong]

Hello, 

I have a dataset with patient_ID and patients' diagnosis codes. If a patient had more than one diagnosis, they are in multiple rows. In another words, the dataset is not unique by patient_ID.  

I would like to create a counter, which is the maximum number of diagnosis a patient can have. 

Can somebody tell me, whether the codes below are creating this counter? 

proc sql noprint;
select left(trim(put(MAX(count),5.))) into: Idy from
(select count(*) as count
from data
group by patient_id);
QUIT;
%PUT &Idy;

Thank you very much.

Best.

Yanyan

[RW9]

"Can somebody tell me, whether the codes below are creating this counter? " - why not just run the code and see?

[PeterClemmensen]

No. Nobody can tell you, whether the codes below are creating this counter, because we can not see your data.

Provide some sample of what your data looks like. It makes it much easier to help 🙂

[ballardw]

---

@YGong wrote:

Hello, 

I have a dataset with patient_ID and patients' diagnosis codes. If a patient had more than one diagnosis, they are in multiple rows. In another words, the dataset is not unique by patient_ID.  

I would like to create a counter, which is the maximum number of diagnosis a patient can have. 

Yanyan

---

Code reading your data  can at best provide the maximum that have occurred. It will never actually provide the number of diagnosis that patient may have.

Example: The day after you run the data and get X as the maximum count the patient with that count is diagnosed with something.

[YGong]

Thanks for your reply!

The data is not supposed to be dynamic. It is supposed to be historical data. I do not have data to test. I was writing data based on a technical notes.

[YGong]

If you are a good SAS programmer, you should know what that codes will do. I am writing the codes based on a technical notes, I do not have data to test. Otherwise I would not ask the question.

[s_lassen · Accepted solution]

@YGong:

I would change the code to something like

proc sql noprint;
  select MAX(count) into: Idy trimmed from
    (select count(distinct diagnosis) as count
    from data
    group by patient_id);
QUIT;
%PUT &Idy;

The first change is just simplifying your select expression, and using the TRIMMED keyword to trim leading and trailing blanks from the macro variable. The second is using count(distinct diagnosis) instead of count(*), which will solve two problems:

1. If there are records with missing diagnosis, these will not be counted
2. If there are repeat diagnoses for some patients, only unique values will be counted.

Of course, if you are absolutely sure that you only have unique, non-missing diagnoses, your count(*) will work just as well. 

[YGong]

Great, thank you so much!!

This is the answer I was looking for. Really appreciated your reply. 

Best regards from Paris.

Yanyan