Removing space while resolving macros

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Removing space while resolving macros

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


Accepted Solutions
Solution
‎08-14-2017 02:08 PM
Super User
Posts: 10,020

Re: Removing space while resolving macros

[ Edited ]

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 () ;

View solution in original post


All Replies
Contributor
Posts: 29

Re: Removing space while resolving macros

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

Super Contributor
Posts: 644

Re: Removing space while resolving macros

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



Contributor
Posts: 46

Re: Removing space while resolving macros

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:

hi

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));

Solution
‎08-14-2017 02:08 PM
Super User
Posts: 10,020

Re: Removing space while resolving macros

[ Edited ]

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 () ;

Contributor
Posts: 29

Re: Removing space while resolving macros

Nice one Ksharp. I will use that in future.

Thanks,

Tim

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 6927 views
  • 4 likes
  • 5 in conversation