BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Babloo
Rhodochrosite | Level 12

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

Babloo
Rhodochrosite | Level 12

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

Kurt_Bremser
Super User

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

Babloo
Rhodochrosite | Level 12

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;

MadhuKorni
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

MadhuKorni
Quartz | Level 8

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

Babloo
Rhodochrosite | Level 12

It's not fixed like my sample data.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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