topic Re: how to remove special characters? in SAS Programming

how to remove special characters?

Babloo — Mon, 24 Aug 2015 08:56:32 GMT

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.

Re: how to remove special characters?

RW9 — Mon, 24 Aug 2015 09:16:09 GMT

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.

Re: how to remove special characters?

Babloo — Mon, 24 Aug 2015 09:26:53 GMT

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

Re: how to remove special characters?

Babloo — Mon, 24 Aug 2015 10:01:15 GMT

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

Re: how to remove special characters?

MadhuKorni — Mon, 24 Aug 2015 10:08:52 GMT

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

Re: how to remove special characters?

Babloo — Mon, 24 Aug 2015 10:13:15 GMT

It's not fixed like my sample data.

Re: how to remove special characters?

RW9 — Mon, 24 Aug 2015 10:27:37 GMT

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.

Re: how to remove special characters?

Kurt_Bremser — Mon, 24 Aug 2015 10:28:00 GMT

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

Re: how to remove special characters?

Babloo — Mon, 24 Aug 2015 10:43:38 GMT

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;

Re: how to remove special characters?

MadhuKorni — Mon, 24 Aug 2015 10:45:22 GMT

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

Re: how to remove special characters?

Kurt_Bremser — Mon, 24 Aug 2015 10:54:52 GMT

PLEASE, would you care to READ?

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

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.