PROC SQL using where clause for multiple columns with multiple values

[prjadhav00]

I am executing a query which has multiple columns in where clause which has multiple values. I know that in PROC SQL you can use IN condition to satisfy and get the correct output.

FAKE DATA

Question: CHF is defined using the following diagnosis codes:
398.91, 402.11, 402.91, 404.11, 404.13, 404.91, 404.93 (Codes are in CHARATER form)

1.Query: (I merge two tables)

proc sql;
create table BENE_CHF as
select distinct a.BENE_ID, b.ICD9_DGNS_CD_1, ICD9_DGNS_CD_2, ICD9_DGNS_CD_3, ICD9_DGNS_CD_4, ICD9_DGNS_CD_5,ICD9_DGNS_CD_6, ICD9_DGNS_CD_7, ICD9_DGNS_CD_8,
LINE_ICD9_DGNS_CD_1,
LINE_ICD9_DGNS_CD_2,
LINE_ICD9_DGNS_CD_3,
LINE_ICD9_DGNS_CD_4,
LINE_ICD9_DGNS_CD_5,                                              THIS QUERY IS WORKING
LINE_ICD9_DGNS_CD_6,
LINE_ICD9_DGNS_CD_7,
LINE_ICD9_DGNS_CD_8,
LINE_ICD9_DGNS_CD_9,
LINE_ICD9_DGNS_CD_10,
LINE_ICD9_DGNS_CD_11,
LINE_ICD9_DGNS_CD_12,
LINE_ICD9_DGNS_CD_13
from work.beneficiary_summary_file_2008 as a,
work.carrier_2008 as b
where a.BENE_ID = b.BENE_ID;
quit;

/***********************************************************************************************************/

Next sept is to sort the data according to ICD codes methioned above.

I tried different quries such as;

1.proc sql;
select *
from bene_chf
where ICD9_DGNS_CD_1 and
ICD9_DGNS_CD_2 and
ICD9_DGNS_CD_3 and
ICD9_DGNS_CD_4 and
ICD9_DGNS_CD_5 and
ICD9_DGNS_CD_6 and
ICD9_DGNS_CD_7 and
ICD9_DGNS_CD_8 and
LINE_ICD9_DGNS_CD_1 and
LINE_ICD9_DGNS_CD_2 and
LINE_ICD9_DGNS_CD_3 and
LINE_ICD9_DGNS_CD_4 and
LINE_ICD9_DGNS_CD_5 and
LINE_ICD9_DGNS_CD_6 and
LINE_ICD9_DGNS_CD_7 and
LINE_ICD9_DGNS_CD_8 and
LINE_ICD9_DGNS_CD_9 and
LINE_ICD9_DGNS_CD_10 and
LINE_ICD9_DGNS_CD_11 and
LINE_ICD9_DGNS_CD_12 and
LINE_ICD9_DGNS_CD_13 in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830');
quit;

2.proc sql;
select *
from bene_chf
where ICD9_DGNS_CD_1
 in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or  
ICD9_DGNS_CD_2
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_3
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_4
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_5
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830')
group by bene_id;
quit;

3.where strip(ICD9_DGNS_CD_1) in ('39891', '40211', '40291', '40411', '40413', '40491', '400493', '42830') or
strip(ICD9_DGNS_CD_2) in ('39891', '40211', '40291', '40411', '40413', '40491', '400493', '42830').........so on

Not able to get a right output.

any suggestion or comments are highly appriciated.

[prjadhav00]

I am executing a query which has multiple columns in where clause which has multiple values. I know that in PROC SQL you can use IN condition to satisfy and get the correct output.

FAKE DATA

Question: CHF is defined using the following diagnosis codes:
398.91, 402.11, 402.91, 404.11, 404.13, 404.91, 404.93 (Codes are in CHARATER form)

1.Query: (I merge two tables)

proc sql;
create table BENE_CHF as
select distinct a.BENE_ID, b.ICD9_DGNS_CD_1, ICD9_DGNS_CD_2, ICD9_DGNS_CD_3, ICD9_DGNS_CD_4, ICD9_DGNS_CD_5,ICD9_DGNS_CD_6, ICD9_DGNS_CD_7, ICD9_DGNS_CD_8,
LINE_ICD9_DGNS_CD_1,
LINE_ICD9_DGNS_CD_2,
LINE_ICD9_DGNS_CD_3,
LINE_ICD9_DGNS_CD_4,
LINE_ICD9_DGNS_CD_5,                                              THIS QUERY IS WORKING
LINE_ICD9_DGNS_CD_6,
LINE_ICD9_DGNS_CD_7,
LINE_ICD9_DGNS_CD_8,
LINE_ICD9_DGNS_CD_9,
LINE_ICD9_DGNS_CD_10,
LINE_ICD9_DGNS_CD_11,
LINE_ICD9_DGNS_CD_12,
LINE_ICD9_DGNS_CD_13
from work.beneficiary_summary_file_2008 as a,
work.carrier_2008 as b
where a.BENE_ID = b.BENE_ID;
quit;

/***********************************************************************************************************/

Next sept is to sort the data according to ICD codes methioned above.

I tried different quries such as;

1.proc sql;
select *
from bene_chf
where ICD9_DGNS_CD_1 and
ICD9_DGNS_CD_2 and
ICD9_DGNS_CD_3 and
ICD9_DGNS_CD_4 and
ICD9_DGNS_CD_5 and
ICD9_DGNS_CD_6 and
ICD9_DGNS_CD_7 and
ICD9_DGNS_CD_8 and
LINE_ICD9_DGNS_CD_1 and
LINE_ICD9_DGNS_CD_2 and
LINE_ICD9_DGNS_CD_3 and
LINE_ICD9_DGNS_CD_4 and
LINE_ICD9_DGNS_CD_5 and
LINE_ICD9_DGNS_CD_6 and
LINE_ICD9_DGNS_CD_7 and
LINE_ICD9_DGNS_CD_8 and
LINE_ICD9_DGNS_CD_9 and
LINE_ICD9_DGNS_CD_10 and
LINE_ICD9_DGNS_CD_11 and
LINE_ICD9_DGNS_CD_12 and
LINE_ICD9_DGNS_CD_13 in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830');
quit;

2.proc sql;
select *
from bene_chf
where ICD9_DGNS_CD_1
 in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or  
ICD9_DGNS_CD_2
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_3
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_4
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_5
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830')
group by bene_id;
quit;

3.where strip(ICD9_DGNS_CD_1) in ('39891', '40211', '40291', '40411', '40413', '40491', '400493', '42830') or
strip(ICD9_DGNS_CD_2) in ('39891', '40211', '40291', '40411', '40413', '40491', '400493', '42830').........so on

Not able to get a right output.

any suggestion or comments are highly appriciated.

[prjadhav00]

I am executing a query which has multiple columns in where clause which has multiple values. I know that in PROC SQL you can use IN condition to satisfy and get the correct output.

FAKE DATA

Question: CHF is defined using the following diagnosis codes:
398.91, 402.11, 402.91, 404.11, 404.13, 404.91, 404.93 (Codes are in CHARATER form)

1.Query: (I merge two tables)

proc sql;
create table BENE_CHF as
select distinct a.BENE_ID, b.ICD9_DGNS_CD_1, ICD9_DGNS_CD_2, ICD9_DGNS_CD_3, ICD9_DGNS_CD_4, ICD9_DGNS_CD_5,ICD9_DGNS_CD_6, ICD9_DGNS_CD_7, ICD9_DGNS_CD_8,
LINE_ICD9_DGNS_CD_1,
LINE_ICD9_DGNS_CD_2,
LINE_ICD9_DGNS_CD_3,
LINE_ICD9_DGNS_CD_4,
LINE_ICD9_DGNS_CD_5,                                              THIS QUERY IS WORKING
LINE_ICD9_DGNS_CD_6,
LINE_ICD9_DGNS_CD_7,
LINE_ICD9_DGNS_CD_8,
LINE_ICD9_DGNS_CD_9,
LINE_ICD9_DGNS_CD_10,
LINE_ICD9_DGNS_CD_11,
LINE_ICD9_DGNS_CD_12,
LINE_ICD9_DGNS_CD_13
from work.beneficiary_summary_file_2008 as a,
work.carrier_2008 as b
where a.BENE_ID = b.BENE_ID;
quit;

/***********************************************************************************************************/

Next sept is to sort the data according to ICD codes methioned above.

I tried different quries such as;

1.proc sql;
select *
from bene_chf
where ICD9_DGNS_CD_1 and
ICD9_DGNS_CD_2 and
ICD9_DGNS_CD_3 and
ICD9_DGNS_CD_4 and
ICD9_DGNS_CD_5 and
ICD9_DGNS_CD_6 and
ICD9_DGNS_CD_7 and
ICD9_DGNS_CD_8 and
LINE_ICD9_DGNS_CD_1 and
LINE_ICD9_DGNS_CD_2 and
LINE_ICD9_DGNS_CD_3 and
LINE_ICD9_DGNS_CD_4 and
LINE_ICD9_DGNS_CD_5 and
LINE_ICD9_DGNS_CD_6 and
LINE_ICD9_DGNS_CD_7 and
LINE_ICD9_DGNS_CD_8 and
LINE_ICD9_DGNS_CD_9 and
LINE_ICD9_DGNS_CD_10 and
LINE_ICD9_DGNS_CD_11 and
LINE_ICD9_DGNS_CD_12 and
LINE_ICD9_DGNS_CD_13 in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830');
quit;

2.proc sql;
select *
from bene_chf
where ICD9_DGNS_CD_1
 in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or  
ICD9_DGNS_CD_2
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_3
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_4
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830') or
ICD9_DGNS_CD_5
in ('39891', '40211', '40291', '40411', '40413', '40491', '40493', '42830')
group by bene_id;
quit;

3.where strip(ICD9_DGNS_CD_1) in ('39891', '40211', '40291', '40411', '40413', '40491', '400493', '42830') or
strip(ICD9_DGNS_CD_2) in ('39891', '40211', '40291', '40411', '40413', '40491', '400493', '42830').........so on

Not able to get a right output.

any suggestion or comments are highly appriciated.

[PGStats]

Is the diagnosis code "398.91", as in your introduction, or "39891" in your queries?

[prjadhav00]

My mistake diagnosis code does not have any decimal value

'39891' is in right form in queries

[ballardw]

---

@prjadhav00 wrote:

Not able to get a right output.

any suggestion or comments are highly appriciated.

---

Example input and example of the "right output" are likely needed.

[ballardw]

Not able to get a right output.

any suggestion or comments are highly appriciated.

[ballardw]

Not able to get a right output.

any suggestion or comments are highly appriciated.