Adding zeros before and after string

[Amrita Sanyal]

Hello,

I've downloaded some course codes from the internet that look like "09.0111", "90.0100" and "90.1000". Now, I need the leading zeros, as well as those after the decimal point to remain, but when I import them to Stata (from an Excel file), the zeros are omitted, even if the course code variable is imported as a string. They look like: "9.0111", "90.01" and "90.1".

How do I add the zeros back to the strings? Obviously, only certain observations need to have the added zeros, not all. All the codes need to be 6 digits long.

Thank you,
Amrita Sanyal.

[Clyde Schechter]

As you do not show the code you used for the importation, it is anybody's guess what you mean by "even if the course code variable is imported as a string." I can tell you that setting the variable to string in Excel will not help you here. What you can do is:

import excel name_of_spreadsheet_file, allstring(format %07.4f)

Now, the problem is, that everything else in the spreadsheet will also be imported as a string variable, which is often a major nuisance. So what I recommend is doing a separate -import excel- command for just the course number variable(s) and whatever other minimal set of columns is needed to uniquely identify observations (rows) in your data. Then take the data set you originally imported, -drop- the course code variable(s), and then -merge 1:1- the two data sets together.

[Amrita Sanyal]

Thank you, Clyde. I apologize for not showing the code. The computer I'm using to post doesn't have Stata. I'm using Stata on another computer (without internet).

I do have another related question. Suppose the data in Excel is in numeric form, i.e. it looks like "9.0111", "90.01" and "90.1". But I know it should look like "09.0111", "90.0100" and "90.1000". Suppose I import it as a numeric variable in Stata. Within Stata, is there a way to convert it to a string with the 6 digits?

Best,
Amrita.

[Clyde Schechter]

If you already have it imported as a numeric variable you can do

tostring the_numeric_variable, replace format(%07.4f)

Now, this command can be "finicky." You may get a message saying the variable "cannot be converted reversibly; no replace". If that happens, you can override its hesitancy by adding a -force- option to the command. But before you just jump in and do this, you need to be sure that the message isn't being caused by a real problem with the numeric variable. So before adding the -force- option and trying again, I would do something like this:

browse the_numeric_variable if the_numeric_variable != real(string(the_numeric_variable))

and make sure that everything looks like valid data. If it does, then the error message from -tostring- is just provoked by the fact that the decimal parts of these numbers, when represented internally in binary by Stata, do not correspond exactly to finite precision decimal numbers that can be shown in a string. (A decimal number like 90.1 has no exact finite representation in binary, just like the fraction 1/3 has no exact finite precision representation in decimal.) For your purposes, that is OK, and you can proceed with -force-. (And you will this time get a warning message warning you about loss of information. Don't worry about that.)

[Amrita Sanyal]

Thank you so much, that worked! Fortunately, I didn't get the "cannot be converted reversibly; no replace" message.

Best,
Amrita.