Re: st: Loading long string variables (from SQL) into Stata

[Jen Zhen <jenzhen99@gmail.com>]

Coming back to this earlier post:

Does anyone actually know how to split SQL text columns (string
variables) after each 240 characters and save the resulting columns
(variables) as a new table (dataset), using SQL browser (or maybe even
from within Stata, but I guess that's probably not possible)?

Thanks so much,
JZ


On Tue, Sep 4, 2012 at 6:25 PM, Nick Cox <njcoxstata@gmail.com> wrote:
> It's my understanding that
>
> 1. -odbc-, just like any Stata import command, expects a one-to-one
> mapping between what it imports and the Stata variables that result.
>
> 2. Thus, as you say, it seems that you need to manipulate your data
> outside Stata.
>
> 3. The only alternative is to read directly into Mata.
>
> Note that parts of long strings that are -str244- would not be much
> use inside Stata any way, except in so far as you could re-combine
> them in Mata.
>
> Nick
>
> On Tue, Sep 4, 2012 at 5:15 PM, Jen Zhen <jenzhen99@gmail.com> wrote:
>> The ideal thing would be if it was somehow possible to split the
>> variables upon loading,
>> or to load only the first x characters once and load only the second x
>> characters the second time etc,
>> but I guess if any splitting can only be done after loading then there
>> seems to be no way around having to do the adjustment already in
>> SQL...
>>
>>
>> On Tue, Sep 4, 2012 at 4:53 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>>> -compress- can do nothing to help and not just for the reason you
>>> mention, that the variables in Stata were truncated on input.
>>>
>>> -compress- will only compress variables from one storage type to a
>>> more compact storage type when that is possible without loss of
>>> information. But the longer strings could not be variables to start
>>> with and in any case there would be loss of information.
>>>
>>> Nick
>>>
>>> On Tue, Sep 4, 2012 at 2:41 PM, tashi lama <ltashi32@hotmail.com> wrote:
>>>> I would also like to find the answer. There is _compress but I am not sure it will be any help since the columns are already truncated before it loads. _compress seems to be helpful once the columns are successfully loaded and you want to save the memory.
>>>
>>> By the way, if you are running stata in unix, did you have any problem
>>> loading sql columns with type varchar? I am having an issue while
>>> loading columns with type varchar.
>>>
>>> From: jenzhen99@gmail.com
>>>
>>>>> I`m loading some variables from an SQL database into Stata using the 2
>>>>> lines of code given below.
>>>>> One of them is a very long string variable that gets cut off when
>>>>> loaded into Stata. Is there a way to either increase the maximum
>>>>> length of string variables allowed in Stata, or to automatically split
>>>>> each string at whatever is the maximum number of symbols and put the
>>>>> rest into further string variables?
>>>>> Thanks so much and best regards,
>>>>> JZ
>>>>>
>>>>> local sql "SELECT var1, var2, var3 FROM database WHERE var1>5"
>>>>> odbc load, exec("`sql'") conn("`db'")
>>>
>>> *
>>> *   For searches and help try:
>>> *   http://www.stata.com/help.cgi?search
>>> *   http://www.stata.com/support/statalist/faq
>>> *   http://www.ats.ucla.edu/stat/stata/
>>
>> *
>> *   For searches and help try:
>> *   http://www.stata.com/help.cgi?search
>> *   http://www.stata.com/support/statalist/faq
>> *   http://www.ats.ucla.edu/stat/stata/
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/