Monday, June 27, 2011

To Escape % Character in SAS PROC SQL

Suppose you want to run a query to find all records containing the pattern "REQUIRED" in a certain column, the SQL where clause will look something as follows:
where thetextfield like "%REQUIRED%"
But if you use it inside SAS, SAS will interpret the %REQUIRED as a macro and throw an error.  Here is how you can escape the % character:
%let pattern=%nrstr(%%)REQUIRED%str(%%);
proc sql;
create table new_dataset as
select * from dataset
where thetextfield like "&pattern.";
quit;
You can also put the code inside a macro, and have the pattern "REQUIRED", etc as a parameter to be passed into the macro.
%macro get_records_with_pattern(pattern);
    %let patternlike=%nrstr(%%)&pattern.%str(%%);
    proc sql;
    create table new_dataset as
    select * from dataset
    where thetextfield like "&patternlike.";
    quit;
%mend get_records_with_pattern;
Now you can call the macro with the parameter REQUIRED and many more.
%get_records_with_pattern(REQUIRED);
%get_records_with_pattern(ACCEPTED);
%get_records_with_pattern(PASSED);

2 comments:

Andria BZ said...

Thanks for sharing this valuable post to my knowledge great pleasure to be here SAS has great scope in IT industry. It’s an application suite that can change, manage & retrieve data from the variety of origin & perform statistical analytic on it…
Regards,
sas course in Chennai|sas training institute in Chennai

Raju Kumar said...

This is my first time visit on your site and i have bookmark this for again visit.
sas online training