BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
YGong
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

@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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

PeterClemmensen
Tourmaline | Level 20

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
Super User

@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
Fluorite | Level 6
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
Fluorite | Level 6
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
Meteorite | Level 14

@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
Fluorite | Level 6

Great, thank you so much!!

 

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

Best regards from Paris.

 

Yanyan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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