Sorting out observations by multiple conditions within group

[waterjelly]

Hi, I want to make 2 tables out of one original table.

How can I make these tables? 

<IN>

Patient no.	DRUG_CODE2
1	SMR
1	SMR
1	other
1	NARCOTIC
2	NARCOTIC
2	other
2	other
3	SMR
3	other
4	other
4	other

<WANT>

<Table 1. SMR + NARCOTIC plus SMR+other(Non-NARCOTIC)>

Patient no.	DRUG_CODE2
1	SMR
1	SMR
1	other
1	NARCOTIC
3	SMR
3	other

<Table 2. Non SMR + NARCOTIC plus Other>

Patient no.	DRUG_CODE2
2	NARCOTIC
2	other
2	other
4	other
4	other

[sbxkoenk]

Hello,

Your question is not very clear.

Do you mean:

• table WANT_1 should contain all patients having "<SMR + NARCOTIC> OR <SMR + other(Non-NARCOTIC)>" and
• table WANT_2 should contain all patients having "<Non SMR + NARCOTIC> OR just <other>".

Please clarify.

Anyway, it may help you to transpose your table. That way, it becomes a one-row based query.

data have;
LENGTH Patient_no 8 DRUG_CODE2 $ 10;
input Patient_no DRUG_CODE2 $;
cards;
1 SMR
1 SMR
1 other
1 NARCOTIC
2 NARCOTIC
2 other
2 other
3 SMR
3 other
4 other
4 other
;
run;

PROC TRANSPOSE data=have out=have_trp;
 by Patient_no;
 var DRUG_CODE2;
run;

Koen

[waterjelly]

Hi, I want 

• table WANT_1 should contain all patients having "<SMR + NARCOTIC> AND <SMR + other(Non-NARCOTIC)>" and
• table WANT_2 should contain all patients having "<Non SMR(other) + NARCOTIC> AND just <other>".

Thank you! 

[Ksharp · Accepted solution]

data have;
LENGTH Patient_no 8 DRUG_CODE2 $ 10;
input Patient_no DRUG_CODE2 $;
cards;
1 SMR
1 SMR
1 other
1 NARCOTIC
2 NARCOTIC
2 other
2 other
3 SMR
3 other
4 other
4 other
;
run;

proc sql;
create table want1 as
select * from have
 group by Patient_no
  having sum(DRUG_CODE2='SMR') ne 0;

create table want2 as
select * from have
 group by Patient_no
  having sum(DRUG_CODE2='SMR') eq 0;

quit;

[ballardw]

Programming works on rules.

What are the rules involved for which records end up in which?