topic Re: how to remove comma in SAS Programming

how to remove comma

cho16 — Wed, 20 Sep 2017 14:44:51 GMT

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 !

Re: how to remove comma

Haikuo — Wed, 20 Sep 2017 14:50:04 GMT

substr(code,2)

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

Re: how to remove comma

ballardw — Wed, 20 Sep 2017 14:53:17 GMT

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);

Re: how to remove comma

cho16 — Wed, 20 Sep 2017 15:04:20 GMT

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

Re: how to remove comma

ballardw — Wed, 20 Sep 2017 15:48:50 GMT

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.

Re: how to remove comma

soham_sas — Wed, 20 Sep 2017 17:05:52 GMT

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;

Re: how to remove comma

Ksharp — Thu, 21 Sep 2017 14:45:18 GMT

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;