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

[Steve Nakoneshny <scnakone@ucalgary.ca>]

Jen,

You may want to have to look at -intext- (SCC).

-intext using "foo.txt", gen(bar) length(240)- would solve the character truncation issue. In my experience, you may have to create a small dataset containing this single variable as to not adversely affect the rest of your variables. It may not saolve your problem specifically, but it can't hurt to have a look.

Steve

On 2012-11-30, at 7:01 AM, Jen Zhen wrote:

> 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/


*
*   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/