topic Re: Sorting out observations by multiple conditions within group in SAS Programming

Sorting out observations by multiple conditions within group

waterjelly — Sun, 06 Jun 2021 11:23:01 GMT

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>

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

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

Re: Sorting out observations by multiple conditions within group

sbxkoenk — Sun, 06 Jun 2021 11:56:37 GMT

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

Re: Sorting out observations by multiple conditions within group

waterjelly — Sun, 06 Jun 2021 11:59:30 GMT

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!

Re: Sorting out observations by multiple conditions within group

Ksharp — Sun, 06 Jun 2021 12:13:12 GMT

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;

Re: Sorting out observations by multiple conditions within group

ballardw — Sun, 06 Jun 2021 12:52:24 GMT

Programming works on rules.

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