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
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 () ;
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
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
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));
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 () ;
Nice one Ksharp. I will use that in future.
Thanks,
Tim
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: