Aliasing columns within SQL embedded in a Stata do-file

[Dimitriy V. Masterov]

Is it possible to alias a column in SQL code that is embedded in a Stata do-file. I want to do something like:

#delimit;
odbc load, exec(`"
    SELECT TOP 10 user_id AS "alised_user_id"
    FROM dw_users;
"') clear dsn("mozart") lowercase sqlshow;

The main reason to do this is in cases where there are reserved SQL words that I would like to serve as variable names in Stata, where such restrictions don't bind.

[Joseph Coveney]

I do that all the time, but I've always used square brackets. It does seem to work with double-quotation marks, though, as your example has.

.ÿversionÿ14.0

.ÿ
.ÿsysuseÿauto,ÿclear
(1978ÿAutomobileÿData)

.ÿrenameÿforeignÿnondomestic

.ÿsummarizeÿgear_ratio,ÿmeanonly

.ÿ
.ÿtempfileÿtmpfil0

.ÿquietlyÿexportÿexcelÿusingÿ`tmpfil0',ÿsheet(Auto)ÿfirstrow(variables)

.ÿ
.ÿlocalÿDSNÿExcelÿFiles;DBQ=`tmpfil0';

.ÿ
.ÿ#delimitÿ;
delimiterÿnowÿ;
.ÿlocalÿsql_statementÿSELECTÿmakeÿASÿmodel,ÿnondomesticÿasÿ"foreign"ÿ///
>ÿÿÿÿÿÿÿÿÿFROMÿ[Auto$]ÿWHEREÿgear_ratioÿ>ÿ`r(mean)'ÿORDERÿBYÿmake;

.ÿ#delimitÿcr
delimiterÿnowÿcr
.ÿ
.ÿodbcÿload,ÿexec(`"`sql_statement'"')ÿdsn("`DSN'")ÿclear

.ÿlistÿinÿ1/2,ÿnoobsÿabbreviate(20)

ÿÿ+------------------------+
ÿÿ|ÿÿÿÿÿÿÿmodelÿÿÿÿforeignÿ|
ÿÿ|------------------------|
ÿÿ|ÿAMCÿConcordÿÿÿDomesticÿ|
ÿÿ|ÿÿAMCÿSpiritÿÿÿDomesticÿ|
ÿÿ+------------------------+

.ÿ
.ÿexit

endÿofÿdo-file


.ÿ

[Dimitriy V. Masterov]

Storing the SQL in a local seems to do the trick. Thanks again, Joseph!