BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
santhosh
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

5 REPLIES 5
TimArm
Obsidian | Level 7

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

RichardinOz
Quartz | Level 8

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



suraestar
Calcite | Level 5

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

Ksharp
Super User

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

TimArm
Obsidian | Level 7

Nice one Ksharp. I will use that in future.

Thanks,

Tim

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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