DATA Step, Macro, Functions and more

how to remove special characters?

Accepted Solution Solved
Reply
Super Contributor
Posts: 436
Accepted Solution

how to remove special characters?

I've  a data like this.

Number

[1234567]

[0234567]

[00004]

I've written the code like below to remove square brackets. However, I could not see the leading zeros after exporting the dataset to .csv.  I need leading zeros in my .csv after removing square brackets.

Number_want=left(compress(Number,,'kad'));

Please advise.


Accepted Solutions
Solution
‎08-24-2015 06:54 AM
Super User
Posts: 7,857

Re: how to remove special characters?

PLEASE, would you care to READ?

I supplied the listing of the .csv's I created, and BOTH contain the LEADING ZEROES!!

Do

cat /usr/sas/tir/work/process_id.csv

on the SAS server, or copy the file to your local machine and open it with notepad.

DON'T use Excel, because it recognizes the values as numbers and converts them, and does not display leading zeroes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,991

Re: how to remove special characters?

Just use compress:

data have;

Number="[1234567]"; output;

number="[0234567]"; output;

number="[00004]"; output;

run;

data want;

  set have;

  number=compress(number,"[]");

run;

Note, its not a good idea to call what is obviously a character variable number.

Super Contributor
Posts: 436

Re: how to remove special characters?

You meant to say that we will get a leading zeros in .csv only if we put double quotes in the variable 'Number'?

Super Contributor
Posts: 436

Re: how to remove special characters?

I ran your code and did proc export to .csv, but still I could not see leading zeros in CSV file.

Super User
Posts: 7,857

Re: how to remove special characters?

See this:

data have;

length number $10;

input number;

cards;

[1234567]

[0234567]

[00004]

;

run;

data want;

set have;

number = compress(number,'[]');

run;

data _null_;

set want;

file '$HOME/want1.csv';

if _n_ = 1 then put 'number';

put number;

run;

proc export data=want file='$HOME/want2.csv' dbms=csv replace;

run;

This are the resulting csv files:

$ cat want*

number

1234567

0234567

00004

number

1234567

0234567

00004

Where is the problem?

Correction because of erroneous use of dataset have in export steps

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 436

Re: how to remove special characters?

Posted in reply to KurtBremser

I need to remove square brackets in my variable before exporting to .csv.

I ran this code and could not see the leading zeros in .csv.

data have;

length number $10;

input number;

cards;

[1234567]

[0234567]

[00004]

;

run;

data want;

set have;

number = compress(number,'[]');

run;

proc export data=want outfile='/usr/sas/tir/work/process_id.csv' dbms=csv replace;

run;

Contributor
Posts: 65

Re: how to remove special characters?

data have;

length number $10;

input number;

number=compress(number,'[]');

cards;

[1234567]

[0234567]

[00004]

;

proc export data = have outfile = "path\filename.csv" dbms = csv replace;

run;

Output in CSV :

number

1234567

0234567

00004

Solution
‎08-24-2015 06:54 AM
Super User
Posts: 7,857

Re: how to remove special characters?

PLEASE, would you care to READ?

I supplied the listing of the .csv's I created, and BOTH contain the LEADING ZEROES!!

Do

cat /usr/sas/tir/work/process_id.csv

on the SAS server, or copy the file to your local machine and open it with notepad.

DON'T use Excel, because it recognizes the values as numbers and converts them, and does not display leading zeroes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 65

Re: how to remove special characters?

If the length(here it refers to number of characters) of the variable is fixed, then you can use Z. format or picture format.

Super Contributor
Posts: 436

Re: how to remove special characters?

Posted in reply to MadhuKorni

It's not fixed like my sample data.

Super User
Super User
Posts: 7,991

Re: how to remove special characters?

Sorry, I have just re-read your post.  It has nothing to do with SAS this.  What I believe you are doing is opening the CSV (thats a text comma separated variable file) in Excel.  One of Excel's "Features" is that it will remove preceding zeroes from numbers.  You rproblem is with Excel, and the simplest solution is to not use it for *anything*.  I can't stress that enough.  Now if you open the CSV in Notepad, what do you see, is the data correct?  If it is missing the zeroes, then you will need to examine the code or the options you used to export the data.  If you had taken the proper approach and write a datastep to put out the data, you can put it as you want.

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 580 views
  • 4 likes
  • 4 in conversation