|Date: ||Tue, 24 May 2005 17:54:04 -0800|
|Reply-To: ||David Neal <afdbn@UAA.ALASKA.EDU>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||David Neal <afdbn@UAA.ALASKA.EDU>|
|Subject: ||Re: MS Access and SAS datasets|
|Content-type: ||text/plain; charset=us-ascii|
You are right, the "slightly" was an understatement on my part.(I guess I
was being nice.)
I believe ACCESS is limited to 256 (or 255 I forget which) columns so I am
required to do a bit of reshaping anyway. I will be working with(reshaping)
the data in SAS and then move it into ACCESS. I feel much more comfortable
tweaking it in SAS and then moving it to ACCESS.
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard
Schreier <hs AT dc-sug DOT org>
Sent: Tuesday, May 24, 2005 5:26 PM
Subject: Re: MS Access and SAS datasets
I suspect that you don't need a "slightly" different approach. You probably
need a significantly different one.
Wide tables are clumsy to work with in SAS, but I think in Access the
problems will be much more severe. I would think that the occasion of a
port from one platform to another provides the opportunity to reshape and
streamline. You will probably find it easier to do this before porting,
rather than after.
On Tue, 24 May 2005 09:26:21 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote:
>Thanks for the input. Unfortunately, the datasets are quite wide. One
>of them, for example, has over 1000 variables (columns). Had I been
>involved with the creation of the initial SAS datasets, I would have
>suggested a slightly different approach. Since this wasn't the case,
>I'm stuck working with things the way they are. To complicate things,
>ACCESS won't handle tables that wide so I'm having to mess with he data
>anyway. Your (and Howard's) suggestions will at least get me going on
>the right path. (Maybe I'll just round up some "slave" labor, AKA a
>graduate student, to set up all the lookup tables in ACCESS.)
>Choate, Paul@DDS wrote:
>> Hi David -
>> I agree with Howard that CNTLOUT will give you your formats to develop
>> lookup tables in Access. Alternately if your data isn't large and you
>> don't care about normal form, you could process your variables through
>> formats with put statements and create parallel formatted data.
>> In v9 there's VLABEL, VLABELX, and VARLABEL, etc. You can use VLABEL and
>> array all your _numeric_ and then _character_ columns and create a label
>> There's also the data dictionary which you can query and use the into:
>> command to load a macro, or just dump the labels in a dataset.
>> Personally, I like to use "options VALIDVARNAME=ANY;" and rename my
>> variables as their labels before I port data to external databases, but
>> an analyst, not a programmer, and I can hear several mavens groaning in
>> distance as I write this. ;-)
>> Good luck and hth -
>> Paul Choate
>> DDS Data Extraction
>> (916) 654-2160
>> On Mon, 23 May 2005 14:01:18 -0800, David Neal <afdbn@UAA.ALASKA.EDU>
>>>I'm trying to move several SAS datasets to a single MS Access database.
>>>When I use proc copy to move the data, I get the following note:
>>>NOTE: Copying SCFSAS.PT_MISC to SCFFAS.PT_MISC (memtype=DATA).
>>>NOTE: SAS variable labels, formats, and lengths are not written to DBMS
>>>My problem is that I'm interested in keeping the variable labels and the
>>>formats as well. Is it possible for SAS to automatically create
>> the "lookup
>>>tables" from the formats? Also, is there a way to keep the variable
>>>when I move from SAS to Access?