Inserting leading 0s

[john dattoma]

Hi everyone,

I've looked through these forums, and I know there are several other posts on this, but they don't do exactly what I need them to do, and I can't figure it out, so I thought someone might be able to help. I need to add leading 0s to a variable, say "var1" so the digits or characters add to 8 (123 would become 00000123) so then when I concatenate with another variable say "var2" which has a value of P or " " (blank but not missing), I end up with a variable that looks something like P00004444 with 9 characters. The issue is that some of my values for var1 are already 9 digits and that is the same row that has a "blank" var2. In my code, if I do "tostring" and change the format to %0.8 I get 8 leading 0s and can easily add those to var2 to get my P00004444. However, with my var1 that already has 9 digits, this actually converts to string in scientific notation to truncate it to 8 characters. This causes an issue when I export to CSV because excel does not recognize the actual number, it only recognizes the scientific notation, and the value becomes something like 11000000. The second issue is that when I export to CSV I lose all of those leading 0s.

My data looks something like this:

2222	p
84375437	p
77	p
111111111	p
999999999
99999	p
83687	p
666666669
543264659
766576	9

***Generate Leading 9 digits with leading 0s and PTIN***
tostring var1, gen(var1_str) format("%08.0f")
gen combined = var2+var1_str
gen var1_len = length(var1_str)
replace var1= "0" + var1 if var1_len == 8


This displays how I want it for the most part in Stata, with the exception for the scientific notations. However, the export to CSV drops and of the leading 0s if they don't have a P and the scientific notation is also problematic for the export.

Just to further clarify what I want to see is all values to be 9 characters. So if there is a 5 character var1 I should add 3 0s and add var2. But if there are 9 characters in var1 and there is a P in var2, I need to drop the 1st character and add P. This is why I made it %0.8. If var2 is blank var1 just needs to be 9 characters. Then I need to export this to CSV and keep the display format.

Any help would be much appreciated.

[Bjarte Aagnes]

gen combined = cond(v2=="P", v2 + string(v1,"%08.0f") , string(v1,"%09.0f"))

[William Lisowski]

Perhaps this will start you in a useful direction.

* Example generated by -dataex-. For more info, type help dataex
clear
input long var1 str1 var2
     2222 "p"
 84375437 "p"
       77 "p"
111111111 "p"
999999999 "" 
    99999 "p"
    83687 "p"
666666669 "" 
543264659 "" 
   766576 "9"
end
tostring var1, gen(var1_str) format("%09.0f")
replace var1_str = var2 + substr(var1_str,2,8) if var2!="" & substr(var1_str,1,1)=="0"
list, clean

. list, clean

            var1    var1_str   var2  
  1.        2222   p00002222      p  
  2.    84375437   p84375437      p  
  3.          77   p00000077      p  
  4.   111111111   111111111      p  
  5.   999999999   999999999         
  6.       99999   p00099999      p  
  7.       83687   p00083687      p  
  8.   666666669   666666669         
  9.   543264659   543264659         
 10.      766576   900766576      9

[john dattoma]

Both of these solutions worked great. I just need to export in txt format to get the correct format into Excel. Thanks for all your hlep.

[Leonardo Guizzetti]

You know you can export directly to XLSX files rather than having CSV as the intermediary. See -help export excel-.