SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

deleting repetitive text within a cell

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

deleting repetitive text within a cell

hi,

suppose I have the following table:

Comp Name

The company ABC

The company ABC

The company ABC

The company DEF

The company DEF

The company DEF

That is, inside each cell I have a repetition, so what I would like to obtain is the deletion of the repetition so that the new table should be as following

Comp Name
The company ABC
The company DEF

Thank you!


Accepted Solutions
Solution
‎02-21-2015 12:38 AM
PROC Star
Posts: 7,363

Re: deleting repetitive text within a cell

Daniel,

Can't say for sure without seeing your sas dataset but, if your file is called have and the variable is actually imported as comp_name, then the following might do what you want:

data want (drop=_Smiley Happy;

  length comp_name $40;

  set have (rename=(comp_name=_c));

  comp_name=scan(_c,1,,'c');

run;

View solution in original post


All Replies
Super User
Posts: 17,840

Re: deleting repetitive text within a cell

SAS doesn't really have cells. Is your data originally in Excel?

Does each variable/row contain the repetitive text or are they separate rows?

Super Contributor
Posts: 413

Re: deleting repetitive text within a cell

Hi Reeza,

Actually my original data is in Excel, sorry for not mentioning that, and there in Excel each row contains the repetitive text, just as I showed in my table above.

Thanks

Super User
Posts: 17,840

Re: deleting repetitive text within a cell

What happens when you import it?

Also, can you attach a sample file.

Super Contributor
Posts: 413

Re: deleting repetitive text within a cell

When I do a proc print of the imported excel file I get:

1ALCO Stores, Inc. ALCO Stores, Inc. ALCO Stores, Inc

that is, the repetition is put into a sequence

Solution
‎02-21-2015 12:38 AM
PROC Star
Posts: 7,363

Re: deleting repetitive text within a cell

Daniel,

Can't say for sure without seeing your sas dataset but, if your file is called have and the variable is actually imported as comp_name, then the following might do what you want:

data want (drop=_Smiley Happy;

  length comp_name $40;

  set have (rename=(comp_name=_c));

  comp_name=scan(_c,1,,'c');

run;

Super Contributor
Posts: 413

Re: deleting repetitive text within a cell

Hi Arthur,

I did your code and it worked for my sample, here is the result:

1

ALCO Stores, Inc.

Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 481 views
  • 0 likes
  • 3 in conversation