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 !
substr(code,2)
If you always have ',' as the first char.
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);
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 |
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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.