topic Re: Create a macro variable with count of diagnosis in SQL in SAS Programming

Create a macro variable with count of diagnosis in SQL

YGong — Mon, 27 Nov 2017 14:55:37 GMT

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

Re: SQL

RW9 — Mon, 27 Nov 2017 14:54:51 GMT

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

Re: SQL

PeterClemmensen — Mon, 27 Nov 2017 14:55:05 GMT

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 🙂

Re: Create a macro variable with count of diagnosis in SQL

ballardw — Mon, 27 Nov 2017 15:29:12 GMT

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

Re: Create a macro variable with count of diagnosis in SQL

YGong — Mon, 04 Dec 2017 21:42:34 GMT

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.

Re: Create a macro variable with count of diagnosis in SQL

YGong — Mon, 04 Dec 2017 21:44:30 GMT

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.

Re: Create a macro variable with count of diagnosis in SQL

s_lassen — Tue, 05 Dec 2017 08:37:51 GMT

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

If there are records with missing diagnosis, these will not be counted
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.

Re: Create a macro variable with count of diagnosis in SQL

YGong — Tue, 05 Dec 2017 09:13:28 GMT

Great, thank you so much!!

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

Best regards from Paris.

Yanyan