BookmarkSubscribeRSS Feed
inquistive
Quartz | Level 8

Hi Experts,

I have a character variable with long values. 

VarName                                 Value

description                             'Fair weather expected todaybut tomorrow's  will be inclement' (please notice todaybut and absence of full stops/periods)

I have imported the data from an excel (97-2003) worksheet and applied LEFT, UPCASE AND COMPRESS functions to clean and left align it in SAS table.

When I tried separating the conjoined  todaybut and add periods to modify it ('Fair weather expected today. But tomorrow's will be inclement.

if description=  'Fair weather expected todaybut tomorrow's  will be inclement'  

then description= 'Fair weather expected today. But tomorrow's will be inclement.' ; run;

It did not work and gave me the original value(s).

Is there  another better way of handling this issue? Thanks in advance.

6 REPLIES 6
japelin
Rhodochrosite | Level 12

how about these code?

data have;
  length description $200;
  description='Fair weather expected todaybut tomorrow''s  will be inclement';
run;

data want1;
  set have;
  if description='Fair weather expected todaybut tomorrow''s  will be inclement'  then
    description='Fair weather expected today. But tomorrow''s will be inclement.';
run;

data want2;
  set have;
  description=strip(tranwrd(description,'todaybut','today. But'))||'.';
run;

 

They both work.

 

 

inquistive
Quartz | Level 8

Thanks for suggestion. That value is one of several values. However, does not solve the problem.

Kurt_Bremser
Super User

Since you have a single quote in the text, you must use double quotes around the text:

data have;
length description $100;
description = "Fair weather expected todaybut tomorrow's  will be inclement";
if description = "Fair weather expected todaybut tomorrow's  will be inclement"  
then description = "Fair weather expected today. But tomorrow's will be inclement."; 
run;
ballardw
Super User

One issue with attempting to insert characters into an existing variable is that depending on the actual length of a specific value and the length of the value after "inserting" you can exceed the defined length and end up with truncated data.

 

Another way instead of testing for along string is to use Tranwrd.

 

data example;
   length x $ 100; /* make sure the example has long enough value to insert*/
                   /* if you skip this then X length is defined using the value shown*/
   x = "Fair weather expected todaybut tomorrow's  will be inclement";
   x = tranwrd(x,'todaybut','today. But');
run;

If you have very many records and/or variables this becomes extremely tedious and you would want to use a tool intended to fix such stuff.

inquistive
Quartz | Level 8
Thanks,
I have description length set as $ 100.Your code and shared by others above are okay and run fine on standalone basis. But the variable is part of a larger program that has to handle with various dirty data issues and is not helping in totality although everything else is cleaned up but this monster string is not cooperating at all.
Thank you all though.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 427 views
  • 5 likes
  • 4 in conversation