adding leading zeros and changing to string

[geneshackman]

Hi all. I have a data set that has Geo ID in a column. It's ID for MCD, Minor Civil Division, and should be 5 digits long. Some of the numbers -should- have leading zeros. So Geo IDs in the data file like "210" should be "00210". But the file got manipulated some, and now it's a csv file and the leading zeros got removed. How can I code, in sas, to get back leading zeros and make this a string field. Some of the values need to add two leading zeros and some need to add only one leading zero. I'm including part of the file, with the GEO ID column. I'd like to leave the original column there and add a new column, called "Geo_ID_Txt" as the new geo id that's string and that has the leading zeros.

Thanks

[Tom · Accepted solution]

You attached an XLSX file and not a CSV file.  If you actually have a CSV then read it with a data step and you can define the variable as character to begin.

Otherwise to recreate a character variable from the numeric variable use the PUT() function with the the Z format to generate leading zeros.

So if you have variable named NUMBER in a dataset named HAVE you create a new dataset named WANT with a new variable named STRING like this:

data want;
  set have;
  string = put(number,z5.);
run;

[geneshackman]

Hi Tom. Thanks for your response. I think that does it.

[geneshackman]

Hi Tom again. Yes, I attached an excel instead of csv. Oops. Sorry.

[ballardw]

---

@geneshackman wrote:

Hi all. I have a data set that has Geo ID in a column. It's ID for MCD, Minor Civil Division, and should be 5 digits long. Some of the numbers -should- have leading zeros. So Geo IDs in the data file like "210" should be "00210". But the file got manipulated some, and now it's a csv file

---

Where is the original source file that was read into???  Who "manipulated" the file and how? Did you "look" at that CSV with Excel and save the file? That is one of the changes that Excel will make to CSV: remove leading zeroes because Excell assumes the value is numeric and leading zero is not kept.

Note, the typical cause of things like variables being of the incorrect type, ID values that you do not intend to do arithmetic with should be character not numeric, is reliance on guessing steps like Proc Import or widgets to read data that are not properly supervised.

If you read the source data correctly with a data step, like many respondents to your questions have done, then the type is set correctly. Import guesses. Separately for each file read.

If your data set is as you say, then  using a Z5. format to write the value may be what you want. Do that when you write it out, as that seems to be what you need.

You are compounding and demonstrating that lack of care by providing XLSX files. They are not SAS data sets and WE have to make assumptions to turn that into a data set. Which assumptions cause just as many products. You should provide data step of your data, not XLSX.

I know that this has been brought up in other of your questions, including bits about opening CSV in Excel changing values.