topic Re: PROC SQL CONTAINS doesnt give me the desire result in SAS Programming

PROC SQL CONTAINS doesnt give me the desire result

imdickson — Tue, 11 Dec 2018 03:21:32 GMT

Hi everyone, i tried to filter some data that i want within a dataset. Lets have a look at the same data:

Table A
pmu ssu assetflag type
aa bb bb cc dd cc
11 22 22 33 44 44
xx yy yy zz pp pp
ee ff ff gg hh ee

I want to filter out data that "type" has in "assetflag".

My SQL Statement:

proc sql;
select * from a
where
assetflag contains type;
quit;

However, i do not see the result at all. 0 records.

What is wrong? BTW, i remember seeing someone posted that CONTAINS to follow with &&&.....however, i think that only applies to macro variable. Correct me if im wrong

Re: PROC SQL CONTAINS doesnt give me the desire result

ChrisNZ — Tue, 11 Dec 2018 03:27:26 GMT

What happens if you use

where ASSETFLAG contains strip(TYPE);

Re: PROC SQL CONTAINS doesnt give me the desire result

imdickson — Tue, 11 Dec 2018 03:36:04 GMT

It seems to me the filter with that statement will not have any effect at all. I still get the same result set(FULL) if i use that.

DO i need to wrap both side of the variable with double quote?

Re: PROC SQL CONTAINS doesnt give me the desire result

ChrisNZ — Tue, 11 Dec 2018 03:40:27 GMT

I still get the same result set(FULL) if i use that.

No you don't.

data HAVE;
 input (PMU  SSU   ASSETFLAG   TYPE) (& $);
 cards;
aa     bb    bb cc dd    cc
11     22    22 33 44    44
xx     yy    yy zz pp    pp
ee     ff     ff gg hh   ee
run;
proc sql;
 select * from HAVE where ASSETFLAG contains strip(TYPE);
quit

PMU	SSU	ASSETFLAG	TYPE
aa	bb	bb cc dd	cc
11	22	22 33 44	44
xx	yy	yy zz pp	pp