Wild character question

[RafiRahi]

The variable APP_ID - is it character variable? If it is then the below code is working in my 9.2 version of base SAS to find any number which has the value '61738495' in the string -

WHERE APP_ID CONTAINS '61738495' ;

If it the above code does not work then please let me know if the variable APP_ID is numeric.

Thanks.

Rafi

[podarum]

The error I get with CONTAINS is this :

7989: Invalid operand for the CONTAINS operator. The operand must have a Period data type that is comparable to the other operand.

[Patrick]

So what are the data types of the variables in your 2 tables. They must be character in order for a LIKE or CONTAINS to work.

[podarum]

They're both numeric.

[podarum]

APP_ID is numeric.

[RW9]

Then you would need to (Oracle sytax again) cast the numeric into a character string to use the like % syntax.  In SAS it would be put(variable,best.).

[podarum]

Basically I have a field in my local data that is chopped (eg.  '61738495') and am trying to match it to another field in my data warehouse that is not chopped (eg.  '6005617384952') .  Have been using

on INDEX (TRIM(a.APP_ID_DW), TRIM(put(b.APP_ID_FILE, 12.))) > 0   that didn't work, I get an error like Function TRIM requires a character expression as arguement 1.. Even if I make APP_ID_FILE a character or numeric.

[RafiRahi]

Could you please try this -

WHERE PUT( APP_ID, 8.) CONTAINS  '61738495' ;

[podarum]

Got this error :

3706 : Syntax error : Data type "APP_ID" does not match a Defined Type name.

[RafiRahi]

Duh! Now I am not sure how to resolve it!

[pp2014]

Try

where cats(APP_ID)  like '%61738495%';

[podarum]

same error as before (using cats(APP_ID) like

3706 : Syntax error : Data type "APP_ID" does not match a Defined Type name.

[stat_sas]

As your both variables are numeric you need to convert both of them in char

data have;

input APP_ID_DW APP_ID_FILE;

datalines;

61738495 6005617384952

;

proc sql;

select * from have

where put(APP_ID_FILE,best16.) contains strip(put(APP_ID_DW,best16.));

quit;

[podarum]

Awesome.. this worked..