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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 33121 views
  • 4 likes
  • 5 in conversation