Date: Fri, 10 Feb 2006 17:02:52 -0500
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: Need to debug codes. thanks
Content-Type: text/plain; charset=ISO-8859-1
Take a look at:
proc sql;
select ptid,
case
when exists (select '' from a
where ptid=all_id.ptid and
el='N' and
visit='I'
)
or
exists (select '' from b
where ptid=all_id.ptid and
el='N'
)
then 1
when exists (select '' from a
where ptid=all_id.ptid and
el='N' and
visit in ('XD', 'U1')
)
then 2
when exists (select '' from a
where ptid=all_id.ptid and
el is null and
visit='I'
)
or
exists (select '' from b
where ptid=all_id.ptid and
el is null
)
then .
else 0
end
as elig
from (select ptid from a
union
select ptid from b
union
select ptid from c
) as all_id
;
quit;
The source is a composite of all IDs from the 3 tables. The CASE structure
closely parallels your narrative of the rules. It should be possible to
employee some UNIONs in the subqueries to reduce the number of EXISTS
conditions, but for now I find this form easier to read.
Results:
PTID elig
403102997009 0
403102997010 0
403193906004 0
403193906006 0
403193906007 0
403193906008 0
403193906010 .
403193906011 0
403193906012 0
403193906013 0
403193906014 0
403193906015 0
403193906017 0
403193906018 0
403193906019 0
403193906020 .
403193906021 0
403193906022 0
403193906023 .
403193906024 0
403210889001 0
403210889007 0
403210889008 0
403210889009 1
403210889010 0
403210889011 0
403210889012 0
403426680009 0
403426680010 2
403426680011 0
Data loader:
data B;
input PTID :$12. EL :$1. VISIT :$3.;
cards;
403193906004 Y D
403193906006 Y D
403193906007 Y D
403193906008 Y D
403193906010 . D
403193906011 Y D
403193906012 Y D
403193906013 Y D
403193906014 Y D
403193906015 Y D
403193906017 Y D
403193906018 Y D
403193906019 Y D
403193906020 . D
403193906021 Y D
403193906022 Y D
403193906023 . D
403193906024 Y D
403210889001 Y D
;
data A;
input PTID :$12. EL :$1. VISIT :$3.;
cards;
403426680009 Y I
403426680010 Y I
403426680010 N XD
403426680011 Y I
403426680011 Y XD
403210889007 Y XD
403210889008 Y I
403210889008 Y XD
403210889009 N I
403210889010 Y I
403210889010 Y XD
403210889011 Y I
403210889011 Y U1
403210889012 Y I
;
data C;
input PTID :$12. VISIT :$3.;
cards;
403102997009 V1
403102997009 U1
403102997009 U1
403102997009 XD
403102997009 XV1
403102997009 XV2
403102997009 I
403102997009 D
403102997009 V1
403102997009 U1
403102997009 U1
403102997009 XD
403102997009 XV1
403102997009 XV2
403102997010 XD
403102997010 D
403102997010 V1
403102997010 XD
403102997010 XD
;
On Thu, 9 Feb 2006 07:20:24 -0800, jingtailan@gmail.com
<jingtailan@GMAIL.COM> wrote:
>Objective: subject eligibility ,create Elig by ID.
>
> If A.EL = N (where VISIT = I) or B.EL = N then elig = 1;
>
>Otherwise if A.EL = N (where VISIT =XD or U1) then elig = 2;
>
>Otherwise if el = missing on either A (where VISIT = I) or B, including
>
>missing dispensing visits, then elig = missing;
>
>Otherwise elig= 0 (use data set C)
>---------------------------------------------------------------------------
-----------------------------------------------
>
>QC check: Check for any missing elig.
>Check for records where either A.el = missing (where VISIT = I) or B.el
>
>=missing and the other variable = Y.
>==================================================================
>
>????I have no problem to create elig in (0,1,2) but have problem with
>elig=missing. If some one can help me. Thank you in advance.
>
>
>Data set B.
> PTID EL VISIT
> 403193906004 Y D
> 403193906006 Y D
> 403193906007 Y D
> 403193906008 Y D
> 403193906010 D
> 403193906011 Y D
> 403193906012 Y D
> 403193906013 Y D
> 403193906014 Y D
> 403193906015 Y D
> 403193906017 Y D
> 403193906018 Y D
> 403193906019 Y D
> 403193906020 D
> 403193906021 Y D
> 403193906022 Y D
> 403193906023 D
> 403193906024 Y D
> 403210889001 Y D
>=================================================
>data set A PTID EL VISIT
> 03426680009 Y I
> 403426680010 Y I
> 403426680010 N XD
> 403426680011 Y I
> 403426680011 Y XD
> 403210889007 Y XD
> 403210889008 Y I
> 403210889008 Y XD
> 403210889009 N I
> 403210889010 Y I
> 403210889010 Y XD
> 403210889011 Y I
> 403210889011 Y U1
> 403210889012 Y I
>
>================================================
>data set C(DO not have variable EL)
> PTID VISIT
> 403102997009 V1
> 403102997009 U1
> 403102997009 U1
> 403102997009 XD
> 403102997009 XV1
> 403102997009 XV2
> 403102997009 I
> 403102997009 D
> 403102997009 V1
> 403102997009 U1
> 403102997009 U1
> 403102997009 XD
> 403102997009 XV1
> 403102997009 XV2
> 403102997010 XD
> 403102997010 D
> 403102997010 V1
> 403102997010 XD
> 403102997010 XD
> =========================================================================
>result desired:
>
> PTID EL VISIT
>ELIG
> 03426680009 Y I
>0
> 403426680010 Y I 0
> 403426680010 N XD 2
> 403426680011 Y I 0
> 403426680011 Y XD 0
> 403210889007 Y XD 0
> 403210889008 I
>
> 403210889008 Y XD 0
> 403210889009 N I 1
> 403210889011 I
> 403193906010 N XD 2
> 403193906011 Y U1 0
> 403193906012 N D 1
> 403193906013 Y D 0
> 403102997010 XD 0
> 403102997010 XV1 0
> 403102997010 XV2 0
