topic Removing space while resolving macros in SAS Programming

Removing space while resolving macros

santhosh — Thu, 02 May 2013 05:17:13 GMT

I am using the following code

data test;
input cprno$ ;
datalines;
CPR006
CPR081
CPR015
CPR022
CPR103
CPR127
CPR136
CPR036
;
run;

proc sql ;
create table CPR as select distinct CPRNO
from test;
quit;

proc sql ;
select CPRNO
into : CPRNO
from CPR;
quit;

proc sql ;
select count(distinct CPRNO)
into :numrows
from CPR;
quit;
%let numrows=&numrows;
%put numrows=&numrows;

%macro CPRNUM;
proc sort data=CPR out=temperv nodupkey;
by CPRNO;
run;
data _null_;
set temperv end=eof;
call symput('CPRNO'!!left(put(_n_,10.)),CPRNO);
if eof then
call symput('numrows',left(put(_n_,20.)));
run;
%put _user_;

%do i= 1 %to &numrows;
/*Connecting to oracle and executing procedure fetching the final table */
proc sql;
connect to oracle(user=developer password="&dbpass" path='cprdev');
execute (execute PROCEDURE_CHECK("&&CPRNO&i")) by oracle;
create table FUN as select * from connection to oracle(select * from CPR_LOG_FINAL ) ;
disconnect from oracle;
quit;
%end;
%MEND CPRNUM;
%cprnum;

i am passing cprno as parameter and executing procedure

while executing procedure i am getting error

identifier 'CPR006 ' too long.

if i am passing 'CPR006' (ie., hard coding ) i am able to execute procedure

how to remove this space while execution

Re: Removing space while resolving macros

TimArm — Thu, 02 May 2013 10:44:22 GMT

Hello santhosh, I think this is a simple solution you could try...

data _null_;

set temperv end=eof;

length t_str $100.;

t_str = cats('CPRNO',put(_n_,10.));

call symput(t_str,CPRNO);

if eof then

t_str = left(put(_n_,20.));

call symput('numrows',t_str);

run;

%put _user_;

Regards,

Tim

Re: Removing space while resolving macros

RichardinOz — Thu, 02 May 2013 10:50:02 GMT

There is a %TRIM() function which can be used in your existing code

execute (execute PROCEDURE_CHECK("%TRIM(&&CPRNO&i)")) by oracle;

Another simpler way to trim a macro variable (including leading spaces) is

%let mvalue = &mvalue ;

but this is not practical in your code.

Richard

Re: Removing space while resolving macros

suraestar — Thu, 02 May 2013 12:18:39 GMT

Hi Santhosh,

To trim macro variables %trim and %left function can be used to remove trailing and leading space.

But In general, while the macro variable is created it does'nt store leading or trailing space in it .

%let test = hi ;

%put &test;

output:

But in your case, you are assigning macro variable 'CPRNO' via datastep and the dataset variable CPRNO may contain leading or trailing space.

So try the below code.

call symput('CPRNO'!!left(put(_n_,10.)),strip(CPRNO));

Re: Removing space while resolving macros

Ksharp — Mon, 14 Aug 2017 18:08:47 GMT

Editor's note: Alot of great solutions that will work but Ksharp has the easiest with using CALL SYMPUTX. This call routine will trim any leading or trailing blanks that may be in the value.

use call symputx () ;

Re: Removing space while resolving macros

TimArm — Thu, 02 May 2013 12:42:30 GMT

Nice one Ksharp. I will use that in future.

Thanks,

Tim