use 'tranwrd' function in a Macro

[Alexxxxxxx]

Hi,

I am using 'tranwrd' function in a Macro, and expect to set '&level.' as 'Rating' in the tranwrd function. Could you please tell me how to do it? I have tried to use ', '', "", but all does not work.

Thanks in advance.

%MACRO sheet(level=,);
data SP_sheet1(keep=SP_Entity_ID SP_ECR_&level. &level. &level._range);
	Length
	SP_Entity_ID $100.
	SP_ECR_&level. $2500.
	SP_ECR_History2 $2500.
	SP_ECR_History3 $3500.
	&level. $50.
	&level._range $200.
	;
	set original_table(drop= LEI_CICI SIC_:);
	SP_Entity_ID=S_P_Entity_ID;
	SP_ECR_&level.=S_P_Entity_Credit_Rating_History;
	SP_ECR_History2=S_P_Entity_Credit_Rating_Histor1;
	SP_ECR_History3=S_P_Entity_Credit_Rating_Histor2;
	SP_ECR_&level.=tranwrd(SP_ECR_&level.,'0D0A'x, ','); 
	SP_ECR_&level.=tranwrd(SP_ECR_&level.,',,', ','); 
	count=countc(SP_ECR_&level.,",")+1;
  do i=1 to count by 2;
  &level.=strip(tranwrd(scan(SP_ECR_&level.,i,','),'&level. :',''));
  &level._range=strip(tranwrd(scan(SP_ECR_&level.,i+1,','),'&level. Range:',''));
  output;
 end; 
run;
%MEND sheet;
%sheet(level=Rating)
run;

[pink_poodle]

You cannot use normal functions on macro variables. They have to be macro functions. I am not aware of %tranwrd, so it would probably have to be %sysfunc(tranword for macro processor to recognize it. Please also check out this post:
https://communities.sas.com/t5/SAS-Programming/How-to-pass-a-macro-variable-through-tranwrd-function...

[PaigeMiller]

Any time you enclose in single quotes some text which includes a macro variable, the macro variable will not resolve. You must use double quotes.

[Shmuel · Accepted solution]

I would like to demonstrate @PaigeMiller's note:

&level.=strip(tranwrd(scan(SP_ECR_&level.,i,','),'&level. :',''));
&level._range=strip(tranwrd(scan(SP_ECR_&level.,i+1,','),'&level. Range:',''));

The RED strings should be enclosed with double-quote and not single quots.

&level.=strip(tranwrd(scan(SP_ECR_&level.,i,','),"&level. :",''));
&level._range=strip(tranwrd(scan(SP_ECR_&level.,i+1,','),"&level. Range:",''));