topic Concatenate Macro Output in SAS Programming

Concatenate Macro Output

sks — Mon, 31 Oct 2011 20:39:19 GMT

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

Concatenate Macro Output

art297 — Mon, 31 Oct 2011 20:48:35 GMT

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.

Re: Concatenate Macro Output

TomCook — Tue, 01 Nov 2011 10:11:51 GMT

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

Re: Concatenate Macro Output

sks — Tue, 01 Nov 2011 14:14:10 GMT

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

Concatenate Macro Output

SASJedi — Tue, 01 Nov 2011 16:19:26 GMT

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;

Re: Concatenate Macro Output

vpn_das — Tue, 22 Nov 2011 08:18:56 GMT

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