Create a macro variable with count of diagnosis in SQL

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Create a macro variable with count of diagnosis in SQL

[ Edited ]

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


Accepted Solutions
Solution
‎12-05-2017 04:12 AM
PROC Star
Posts: 253

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,416

Re: SQL

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

PROC Star
Posts: 1,215

Re: SQL

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 Smiley Happy

Super User
Posts: 13,321

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


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.

 

 

New Contributor
Posts: 4

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

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.
New Contributor
Posts: 4

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

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.
Solution
‎12-05-2017 04:12 AM
PROC Star
Posts: 253

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

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

New Contributor
Posts: 4

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

Great, thank you so much!!

 

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

Best regards from Paris.

 

Yanyan

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 176 views
  • 6 likes
  • 5 in conversation