Align cells in Stata for matching purpose

[Canh Dang]

Hello,

I am trying to align several cells in Stata and would like to do it automatically. I've tried to export as an Excel file, align, then reimport back to Stata but that hasn't worked.

I extract the data as follow:

input long regno int subno long nameno str167 name str134 E str80 F str37 G
200047 1     19 "CLEMENT MARSLAND'S CHARITY                                                                                                                            " "" "" ""
200047 2     20 "HENRY SMITH'S CHARITY (SHARE)                                                                                                                         " "" "" ""
200047 4 525270 "THE REVEREND ROBERT HANCOCK'S CHARITY"                                                                                                                  "" "" ""
200047 3     21 "JOHN ATTERTON                                                                                                                                         " "" "" ""
200047 6     24 "WIDOWS FUND (REV ROBERT HANCOCK'S CHARITY)                                                                                                            " "" "" ""
200047 0 170093 "NORTHILL PAROCHIAL CHARITIES                                                                                                                          " "" "" ""
200047 5     23 "FATHERLESS GIRLS FUND (ELIZABETH HUTCHINSON'S CHARITY)                                                                                                " "" "" ""
end

This is how it looks. Can anyone please explain to me why this is the case? I've checked the variable's format, which is %167s so I think they should be left-aligned.

Thank you in advance.

[Clyde Schechter]

With string variables, the default is right alignment. To get left-alignment the string length has to be specified as negative.

ds, has(type string)
foreach v of varlist `r(varlist)' {
    local vformat: format `v'
    local vformat: subinstr local vformat "%" "%-"
    format `vformat' `v'
}

will left-align all of your string variables.

Or, there is a user-written program -leftalign-, written by Robert Picard, which you can get by running -ssc install leftalign-.

[Clyde Schechter]

Addendum: even had it "worked," carrying out a data management step in Excel is a bad idea. Excel leaves no audit trail of what was done. And if a copy/paste error were made when going in either direction between Stata and Excel, you would be working with a corrupted data set, and probably not discover it until it produced some bizarre results much later on when the data were analyzed. (Worse, it might never be discovered and you would just propagate incorrect results indefinitely.)

Whatever you think of spreadsheets as vehicles for sharing data, for presenting human-readable layouts of data, and for certain other purposes, they are quite unsuitable for data management and analysis.

[Canh Dang]

Both ways work perfectly. Thank you very much for teaching me these two tricks. I really appreciate your help since the last three topics.

And thanks for the Addendum too. I was aware of that using Excel to manage and analyse data should be avoided; I was desperate and tried to see if Excel could help here. Thanks for your note and your valuable advice.