topic Re: how to remove the text after a specific character/words in New SAS User

how to remove the text after a specific character/words

SASnewbie2 — Mon, 23 Dec 2019 10:01:04 GMT

Hi Mate,

Appreciate your advice how can I remove the text after a specific character/words

For instance,

House and Office > Lighting > Floor Lamps
Building and Office > Lighting > Table Lamps
Factory and Office > Lighting > Wall Lamps & Sconces
Home and Office > Lighting > Hanging & Pendant Lights

I would like to search and remove all the words after "Office". Usually I would use the Excel Function "Find and Replace" to find "Office*~ " and replace . However, I can't find a similar function in SAS advanced expression. Appreciate your advice.

Ultimate Result as follows

House and Office
Building and Office
Factory and Office
Home and Office

Re: how to remove the text after a specific character/words

Kurt_Bremser — Mon, 23 Dec 2019 10:31:25 GMT

Your word "Office" is always followed by a ">". Can you use that as the delimiter? Then it would be a simple call of scan().

Re: how to remove the text after a specific character/words

ed_sas_member — Mon, 23 Dec 2019 11:55:36 GMT

Hi @SASnewbie2

You can use for example the PRXCHANGE function.

In the below code, the function looks for the following pattern in variable var1:

- Any character (.) zero, one or more time (*) at the beginning of the string (^)

- Followed by Office

- Followed by any character (.) zero, one or more time (*) at the end of the string ($)

The first group of characters (in rose) is enclosed in ( ) and can be retrieved by using $1

data want;
	set have;
	var2 = prxchange('s/(^.*Office).*$/$1/',1,var1);
run;

Re: how to remove the text after a specific character/words

SASnewbie2 — Tue, 24 Dec 2019 02:30:59 GMT

Is that a simple function or formula I can use it in the advanced expression as shown below.

my data was vary as show below so I can't use a delimiter and I try Scan() function, it doesn't show the result i wanted.Could be there's a mistake on my scan() formula

Factory House and Office > Lighting > Floor Lamps
House Building and Office follows by Lighting then Table Lamps
Factory and Office next Lighting last Wall Lamps & Sconces
Home and Office then Lighting > Hanging & Pendant Lights

Ultimate Result as follows

House and Office
Building and Office
Factory and Office
Home and Office

Re: how to remove the text after a specific character/words

Patrick — Tue, 24 Dec 2019 05:24:46 GMT

Using below data:

data have;
  infile datalines truncover;
  input full_string $80.;
  datalines;
Factory House and Office > Lighting > Floor Lamps
House Building and Office follows by Lighting then Table Lamps
Factory and Office next Lighting last Wall Lamps & Sconces
Home and Office then Lighting > Hanging & Pendant Lights
Home and Work then Lighting > Hanging & Pendant Lights
;

Using the query builder you then can define a computed column want_string using the following formula:

substrn(t1.full_string, 1, ifn(findw(t1.full_string,'Office',' ','ip')>0, findw(t1.full_string,'Office',' ','ip')+5, 0) )

Which then returns the following result:

The IFN() function gets used to return a value of Zero for strings with no word Office in it.