BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sks
Fluorite | Level 6 sks
Fluorite | Level 6

Hi All,

     I have a parameter for my stored process where one of the values the user can select is 'REJECTED', If the user selects 'REJECTED', I want to select all the records that begin with the word 'REJECTED', is there anything in SAS, which will let me check if the value begins with a certain word like 'REJECTED'.

Thank You

Shri

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

sks,

There is a possible problem with using CONTAINS.  For example, you say you only want rows that start with REJECTED in the STATUS variable text.  If STATUS includes the phrase "was not rejected",  the CONTAINS operator would select that observation.  There are simpler ways of concatentating a % sign onto your macro variable value, but I'm  assuming you chose the data step for your own good reasons. So give this code sample a whirl:

  /* Make a data set to play with */
data de_services_statistics;
   infile datalines dlm='*';
   input ID Status $35.;
datalines;
1*Don't choose me.
2*REJECTED - pick me!
3*I'm not rejected.
;
run;

  /* Test the code */
%let de_status=REJECTED;
data _NULL_;
   /* SYMGET retrieves macro variable values at execution time */
   /* The CAT series of functions make text manipulation easier than with the */
   /* concatenation operators (|| or !!) */
   whereclausesummary = CAT("upcase(status) LIKE '",cats(symget('de_status'),"%'"));
   /* SYMPUTX is a newer version of SYMPUT with extra features */
   call symputx("whereclausesummary",whereclausesummary);
run;

proc sql; 
select * from de_services_statistics
   where &whereclausesummary
;
quit;

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

5 REPLIES 5
art297
Opal | Level 21

The answer is yes, but which function or operator to use would depend upon how you want to use it.

e.g., if upcase(variable) =: "REJECTED"

would limit the records to those records where a variable named variable contained a value that at least started with the string rejected, regardless of case.

TomCook
Calcite | Level 5

You could potentially use the LIKE operator in PROC SQL. For example, the following snippet would give you all rows where the subsetting variable begins with 'rejected'.

... where upcase(variable) like 'REJECTED%'

sks
Fluorite | Level 6 sks
Fluorite | Level 6

Tom,

     Thank you for your response, my first choice was to use LIKE too, but the problem is the macro contains the word 'REJECTED' and if I have to use it with Like as you have mentioned, I have to add % symbol at the end of the word Rejected. which I did not know how to do. So for now I have used Contains

%let de_status=REJECTED

data _NULL_;

destatus = cats(&de_status,"%");

put destatus;

call symput("destat"," ' " || destatus || " ' ");

whereclausesummary = "upcase(status) contains &destat")

call symput ("whereclausesummary",whereclausesummary);

select * from de_services_statistics

where &whereclausesummary

SASJedi
SAS Super FREQ

sks,

There is a possible problem with using CONTAINS.  For example, you say you only want rows that start with REJECTED in the STATUS variable text.  If STATUS includes the phrase "was not rejected",  the CONTAINS operator would select that observation.  There are simpler ways of concatentating a % sign onto your macro variable value, but I'm  assuming you chose the data step for your own good reasons. So give this code sample a whirl:

  /* Make a data set to play with */
data de_services_statistics;
   infile datalines dlm='*';
   input ID Status $35.;
datalines;
1*Don't choose me.
2*REJECTED - pick me!
3*I'm not rejected.
;
run;

  /* Test the code */
%let de_status=REJECTED;
data _NULL_;
   /* SYMGET retrieves macro variable values at execution time */
   /* The CAT series of functions make text manipulation easier than with the */
   /* concatenation operators (|| or !!) */
   whereclausesummary = CAT("upcase(status) LIKE '",cats(symget('de_status'),"%'"));
   /* SYMPUTX is a newer version of SYMPUT with extra features */
   call symputx("whereclausesummary",whereclausesummary);
run;

proc sql; 
select * from de_services_statistics
   where &whereclausesummary
;
quit;

Check out my Jedi SAS Tricks for SAS Users
vpn_das
Calcite | Level 5

There is no need to have a data step. The following SQL will do the job.

select * from de_services_statistics

where upcase(status) like upcase("&de_status.%")

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1052 views
  • 0 likes
  • 5 in conversation