BookmarkSubscribeRSS Feed
keen_sas
Quartz | Level 8

The observation consists of text along with the variable names, that variable while concatenating will be converted into value and forms a complete observation. The variable names are embedded  in square brackets. While concatenating they should be stripped to avoid unwanted spaces in the final observation.The number of variables are not fixed , the text may also contain some special characters as well. How to make this functional. Original is the text and output is what is expected,both are displayed below at the end.

 

Tried with the below code.,but something is missing the do loop while splitting the text based on delimiter ']'

 

data a;
text="It was found that the customer transacted an average of [AVERAGE] transaction per month in [START_YEAR] and closed account in [END_YR]." ;
call symputx('txt',countw(text,"]"));
run ;

 

data b;
set a ;
array txt{&txt} $200. ;
do i = 1 to countw(text,"]");
if i =1 then txt{i}=substr(text,1,find(text,"]"));
else txt{i}=substr(text,find(text,"]")+1,find(text,"]"));
end;
run ;

 

Original  Output
It was found that the customer transacted an average of [AVERAGE] transaction per month in [START_YEAR] and closed account in [END_YR]. ; "It was found that the customer transacted an average of" || strip(AVERAGE) || "transaction per month in" || strip(START_YEAR) || "and closed account in" || strip(END_YR).";

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Is it always the same?  Three tranwrds or regreplaces is the easiest:

data b;
  set a;
  text=tranwrd(text,'[AVERAGE]','"||strip(AVERAGE)||"');
  text=tranwrd(text,'[START_YEAR]','"||strip(START_YEAR)||"');
  text=tranwrd(text,'[END_YR]','"||strip(END_YR)||"');
run;
keen_sas
Quartz | Level 8

Thanks for immediate response. But this is not always the same , text differs for each and every iteration , the variables differs and we should also add the quotes for the string before and after identifying the variables.

the original is what is present in the dataset and output is expected . The text in the output is added with quotes ,pipe symbol is prefixed to concatenate the variable and after that again the text is placed in  quoted till the next variables are identified , its repeated till the end of the string.

 

Original  Output
It was found that the customer transacted an average of [AVERAGE] transaction per month in [START_YEAR] and closed account in [END_YR]. ; "It was found that the customer transacted an average of" || strip(AVERAGE) || "transaction per month in" || strip(START_YEAR) || "and closed account in" || strip(END_YR).";
RW9
Diamond | Level 26 RW9
Diamond | Level 26

My code does include quotes.  If it changes each time, then will the text between angular brackets always be the variable?  If so then replace the text

[ with "||strip(   

and the text

] with )||"

E.g.

data want;
  set have;
  text=tranwrd('[','"||strip(');
  text=tranwrd(']',')||"');
run;

Note the double quotes used within the replacement strings.

ballardw
Super User

@keen_sas wrote:

The observation consists of text along with the variable names, that variable while concatenating will be converted into value and forms a complete observation. The variable names are embedded  in square brackets. While concatenating they should be stripped to avoid unwanted spaces in the final observation.The number of variables are not fixed , the text may also contain some special characters as well. How to make this functional. Original is the text and output is what is expected,both are displayed below at the end.

 

Tried with the below code.,but something is missing the do loop while splitting the text based on delimiter ']'

 

data a;
text="It was found that the customer transacted an average of [AVERAGE] transaction per month in [START_YEAR] and closed account in [END_YR]." ;
call symputx('txt',countw(text,"]"));
run ;

 

data b;
set a ;
array txt{&txt} $200. ;
do i = 1 to countw(text,"]");
if i =1 then txt{i}=substr(text,1,find(text,"]"));
else txt{i}=substr(text,find(text,"]")+1,find(text,"]"));
end;
run ;

 

Original  Output
It was found that the customer transacted an average of [AVERAGE] transaction per month in [START_YEAR] and closed account in [END_YR]. ; "It was found that the customer transacted an average of" || strip(AVERAGE) || "transaction per month in" || strip(START_YEAR) || "and closed account in" || strip(END_YR).";

 


Why are you requiring the use of the || operator? And how is the "output" variable used?

I

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1867 views
  • 0 likes
  • 3 in conversation