DATA Step, Macro, Functions and more

how to remove comma

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

how to remove comma

Hi,

 

I want to remove the string ' ,' from the column Code.

 

ID                      CODE

1

,ABD

2

,VBG

3

,NMJ,KJH,LKJ

4

,TYU,IOU,DEF

5

,TYY

6

,JKK

7

,LKJ

8

,MKL

Output Should be as below:

1

ABD

2

VBG

3

NMJ,KJH,LKJ

4

TYU,IOU,DEF

5

TYY

6

JKK

7

LKJ

8

MKL

 

Thanks for your help !


Accepted Solutions
Solution
‎09-20-2017 10:52 AM
Respected Advisor
Posts: 3,162

Re: how to remove comma

substr(code,2)

If you always have ',' as the first char.

 

View solution in original post


All Replies
Solution
‎09-20-2017 10:52 AM
Respected Advisor
Posts: 3,162

Re: how to remove comma

substr(code,2)

If you always have ',' as the first char.

 

Super User
Posts: 13,008

Re: how to remove comma

If it is only a leading , that you do not want then in a data step.

 

if code =: ',' then code = substr(code,2);

 

If ALL of your values have the leading , then you only need:

code = substr(code,2);

Contributor
Posts: 38

Re: how to remove comma

Thank You Ballardw !

 

Can you please let me know how to remove the ',' value at the front and also remove the comma at the end.

 

 

ID                      CODE

1

,ABD,

2

,VBG,

3

,NMJ,KJH,LKJ,

4

,TYU,IOU,DEF,

5

,TYY,

6

,JKK,

7

,LKJ,

8

,MKL

 

Output:

 

ID                      CODE

1

,ABD

2

,VBG

3

,NMJ,KJH,LKJ

4

,TYU,IOU,DEF

5

,TYY

6

,JKK

7

,LKJ

8

,MKL

Super User
Posts: 13,008

Re: how to remove comma

Please describe your data and what you actually need in a bit more detail. If you keep adding rules then the original solution may not be valid.

Do ALL of your values start with a comma?

Do ALL of your values end with a comma?

If the answer to either of these is not yes then you need to process the data conditionally.

 

to test for a comma at the end that needs to be removed:

if substr(code,length(code))=',' then code=substr(code,1,length(code)-1);

 

If there was a leading comma to remove the above line of code would be AFTER the part that removed the leading comma.

 

If you need to always remove both a leading and trailing comma:

code = substr(code,2,length(code)-2);

should work.

Super User
Posts: 10,611

Re: how to remove comma

data comma;
input ID code$ 15.;
cards;
1    ,ABD,
2    ,VBG,
3    ,NMJ,KJH,LKJ,
4    ,TYU,IOU,DEF,
5    ,TYY,
6    ,JKK,
7    ,LKJ,
8    ,MKL
;
run;

data want;
 set comma;
 want=prxchange('s/^,|,$//',-1,strip(code));
run;

Contributor
Posts: 37

Re: how to remove comma

data comma;
input ID code$ 15.;
cards;
1    ,ABD,
2    ,VBG,
3    ,NMJ,KJH,LKJ,
4    ,TYU,IOU,DEF,
5    ,TYY,
6    ,JKK,
7    ,LKJ,
8    ,MKL

;
proc print;
run;

/*to reome the leading the leading comma;*/

data remove_leading_comma;
set comma;
N_code = substr(code,2);
run;

/*to remove all the comma*/

data remove all_comma;
set comma;
N_code = TRANSLATE(code ,"",',');
run;


/*to reomove the trailing comma*/

data remove_trailing_comma;
set comma;
N_code = substr(code,1,length(code)-2);
run;

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 377 views
  • 2 likes
  • 5 in conversation