topic Re: How to identify distinct values in SAS Enterprise Guide

How to identify distinct values

Kalai2008 — Tue, 07 Jan 2020 05:03:11 GMT

Hello,

I have a huge dataset and trying to get summary like below in Proc SQL.

Example:

There are 10 patients having treatment no for each month. Each patient repeats every month (or may not) with different treatment no, but some get repeated with same treatment no.

I am trying to filter out only the patients that have the same treatment no repeated in all given months.

Data Given:

Month patient TreatmentNo

Jan pat1 20

Jan pat2 30

Jan pat2 40

Jan pat3 30

Jan pat6 90

Jan pat10 28

Feb pat1 30

Feb Pat2 40

Feb pat3 50

Mar pat1 10

Mar pat6 90

Mar pat10 28

Data Want:

Prior Month TreatmentNo

Pat2 Jan 40

Pat2 Feb 40

Pat 6 Jan 90

Pat 6 Mar 90

Pat 10 Jan 28

Pat 10 Mar 28

Thanks for checking

Re: How to identify distinct values

PeterClemmensen — Tue, 07 Jan 2020 06:42:03 GMT

Here is a hash object approach

data have;
input Month $ patient $ TreatmentNo;
datalines;
Jan pat1  20
Jan pat2  30
Jan pat2  40
Jan pat3  30
Jan pat6  90
Jan pat10 28
Feb pat1  30
Feb pat2  40
Feb pat3  50
Mar pat1  10
Mar pat6  90
Mar pat10 28
;

data want;
    if _N_=1 then do;
        declare hash h(dataset:'have', multidata:'Y');
        h.definekey('Patient', 'TreatmentNo');
        h.definedone();
    end;

    set have;

    do _N_=1 by 1 while (h.do_over()=0);
        if _N_ > 1 then output;
    end;
run;

Re: How to identify distinct values

ed_sas_member — Tue, 07 Jan 2020 06:44:23 GMT

Hi @Kalai2008

You can try the following code:

proc sql;
	select patient, TreatmentNo, Month
	from have
	group by patient, TreatmentNo
	having count(TreatmentNo) >= 2
	order by patient, Month;
quit;

- if you want to display months in a chronological order, you need to define and then use a format

- beware of the way patients are identified: eg. patient 2 is identified as Pat2 but also pat2 -> you need to harmonize the value

- I am wondering about whether Pat 10 and Pat 6 need to be retrieved or not in the report -> they have the same Treatment (-> repetition) in all given months. Only patient 2 has twice 40 and once 30, so corresponds to your description.

Re: How to identify distinct values

Ksharp — Tue, 07 Jan 2020 11:08:47 GMT

Assuming I understood what you are talking about.

data have;
input Month $ patient $ TreatmentNo;
datalines;
Jan pat1  20
Jan pat2  30
Jan pat2  40
Jan pat3  30
Jan pat6  90
Jan pat10 28
Feb pat1  30
Feb pat2  40
Feb pat3  50
Mar pat1  10
Mar pat6  90
Mar pat10 28
;
proc sql;
create table want as
	select *
	from have as a
	group by patient,TreatmentNo
	having count(distinct month)= 
(select count(distinct month) from have where patient=a.patient)
    ;
quit;

Re: How to identify distinct values

Kalai2008 — Tue, 07 Jan 2020 18:05:51 GMT

Thank you. it worked