Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


From   Nick Cox <njcoxstata@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Loading long string variables (from SQL) into Stata
Date   Tue, 4 Sep 2012 17:25:14 +0100

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/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index