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
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.
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.
They're both numeric.
APP_ID is numeric.
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.).
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.
Could you please try this -
WHERE PUT( APP_ID, 8.) CONTAINS '61738495' ;
Got this error :
3706 : Syntax error : Data type "APP_ID" does not match a Defined Type name.
Duh! Now I am not sure how to resolve it!
Try
where cats(APP_ID) like '%61738495%';
same error as before (using cats(APP_ID) like
3706 : Syntax error : Data type "APP_ID" does not match a Defined Type name.
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;
Awesome.. this worked..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.