DATA Step, Macro, Functions and more

Concatenate Macro Output

Accepted Solution Solved
Reply
Contributor sks
Contributor
Posts: 42
Accepted Solution

Concatenate Macro Output

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


Accepted Solutions
Solution
‎11-01-2011 12:19 PM
SAS Employee
Posts: 104

Concatenate Macro Output

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;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Concatenate Macro Output

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.

New Contributor
Posts: 3

Re: Concatenate Macro Output

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%'

Contributor sks
Contributor
Posts: 42

Re: Concatenate Macro Output

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

Solution
‎11-01-2011 12:19 PM
SAS Employee
Posts: 104

Concatenate Macro Output

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;

N/A
Posts: 1

Re: Concatenate Macro Output

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.%")

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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