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.

David

-----Original Message----- 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 To: SAS-L@LISTSERV.UGA.EDU 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.) > >Thanks again > >David Neal > >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 your >> 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 >> dataset. >> >> 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 I'm >> an analyst, not a programmer, and I can hear several mavens groaning in the >> 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> wrote: >> >> >>>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 >>>tables. >>> >>> >>> >>>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 labels >>>when I move from SAS to Access? >>> >>> >>> >>>David Neal