topic Re: Filter on multiple values in SAS Programming

Filter on multiple values

kanga — Mon, 15 Apr 2019 18:21:27 GMT

Hello, I have a data set, that looks like a change log. Each document number can have 30-50 lines but the document code can be different on each line. Example below... How can I say I want Document numbers that do not have Document cd DEF but do have document code EFG?

Document Cd	Document number
ABC	103013013013
BCD	103013013013
EFG	103013013013
ABC	103013013013
QRS	103013013013
QRS	103013013013
ABC	103013013013
DEF	103013013013

Re: Filter on multiple values

PaigeMiller — Mon, 15 Apr 2019 18:27:55 GMT

How can I say I want Document numbers that do not have Document cd DEF but do have document code EFG?

This is the same as asking for document codes equal to EFG, is it not?

proc sql;
    create table want as select * from have where document_cd='EFG';
quit;

Re: Filter on multiple values

novinosrin — Mon, 15 Apr 2019 18:27:46 GMT

"How can I say I want Document numbers that do not have Document cd DEF but do have document code EFG? "

What do you mean?

What do you want as output for the sample you posted?

Re: Filter on multiple values

kanga — Mon, 15 Apr 2019 18:30:18 GMT

But I only want it if there is no DEF associated with the Document number.

Re: Filter on multiple values

kanga — Mon, 15 Apr 2019 18:31:51 GMT

I want a listing of document numbers that have one of the document codes but not the other. (looking for a type of process that is incomplete)

Re: Filter on multiple values

PaigeMiller — Mon, 15 Apr 2019 18:36:32 GMT

@kanga it would help if you showed us a meaningful example, where the document numbers are not all equal, and also show us the output desired.

Re: Filter on multiple values

novinosrin — Mon, 15 Apr 2019 18:38:31 GMT

Hello @kanga

Assuming I Understand-->


proc sql;
create table want as
select *
from have
group by Document_number
having max(Document_Cd='DEF')=0 and max(Document_Cd='EFG')=1;
quit;

Re: Filter on multiple values

kanga — Mon, 15 Apr 2019 18:47:21 GMT

Here is subset of the actual data set. I want a listing of all DOC_Num that have a DAC in Type Field but do not have an FN2

Type	doc_num	qty	price	dtgrp
FTE	FT6261712371122	55	0.05	201705
DWB	FT6261712371122	55	17.87	201705
FTR	FT6261712371122	55		201705
FT6	FT6261712371122	55		201706
FTF	FT6261712371122	55	17.87	201706
FTR	FT6261712371122	55		201706
FTM	FT6261712371122	55	0.02	201706
D6B	FT6261712371122	25	17.87	201707
FTR	FT6261712371122	25	29.48	201707
FTZ	FT6261712371122	25	0.29	201707
DAC	FT6261712371122	30	0.29	201708
FTR	FT6261712371122	25	17.87	201708
FT6	FT6261712371122	5		201708
FTZ	FT6261712371122	30	4.92	201802
FN2	FT6261712371122	5	0.06	201802
FQ2	FT6261712371122	0	0.06	201806
D6A	P947Z022900177	113	0.02	201707
FN2	P947Z022900177	113		201804
FQ2	P947Z022900177	113		201804
FT6	P947Z022900177	113		201704
FTB	P947Z022900177	113		201705
FTE	P947Z022900177	113		201704
FTM	P947Z022900177	113	0.09	201708
FTP	P947Z022900177	113		201705
FTR	P947Z022900177	113	63.2	201708
FTZ	P947Z022900177	113	392.79	201707
FN2	P947Z022900177	113	0.09	201708
FQ2	P947Z022900177	113	0.09	201709

Re: Filter on multiple values

PaigeMiller — Mon, 15 Apr 2019 18:53:35 GMT

proc freq data=have;
by doc_num;
tables type/noprint list out=b;
run;

proc transpose data=b out=c;
by doc_num;
var count;
id type;
run;

data want;
    set c(where=(dac>0 and fn2<1));
run;

Re: Filter on multiple values

kanga — Mon, 15 Apr 2019 19:45:39 GMT

Thank you!!!!