Yes, "normalize" is Microsoft's response as well.
No, it really doesn't work to normalize; I know it is really hard to understand what this data looks like, but the whole point of putting it in Access is to build data entry and viewing screens for the users, and we need to pull out and merge by characteristics; MS Access just can't even run that kind of query with its 256 variable limitation. Right now, for instance, I'm looking at patterns of haplotypes- the frequencies of combinations of responses over all the observations in a gene and even across a number of genes.
I've got about 3000 gene values, 900 demographic values (patient and family histories), along with other tables with patient-level information that need to be merged by ID together. Building any sort of data entry screens or query screens in Access to handle that sort of data is like jumping through hoops.
I did try to build one data entry table for all our demographic variables, but I wound up with 5 tables for my 900 variables, and then I can't do queries across tables very well, so it is really frustrating to work in that environment, as I just have to pull it back into SAS to do any sort of analysis on it.
Other gene people are dealing with 30,000 or more gene values in additional to the thousands of patient values.
Fortunately, SAS can handle this :-), but I'd love to have an easy front end that my users could access and run queries on without having to know anything about "normalization".
----- Original Message -----
From: data _null_,
Sent: Wednesday, February 20, 2008 3:11 PM
Subject: Re: Error with Proc IMPORT
On Wed, Feb 20, 2008 at 4:05 PM, Mary <email@example.com> wrote:
> Actually, it is STILL 256 in Access 2007...Microsoft does not seem to understand that all the gene people have thousands and thousands of variables...
> ----- Original Message -----
> From: Jim Agnew
> To: SAS-L@LISTSERV.UGA.EDU
> Sent: Wednesday, February 20, 2008 2:47 PM
> Subject: Re: Error with Proc IMPORT
> Probably what it USED to be.. that limit in old Access 2000 was what
> drove us to MySQL, plus one can use the MySQL migration wizard to
> migrate not only data but schemas from access to Mysql...
> one gotcha.... it will recode the yes/no values...
> Frank Mwaniki wrote:
> > <I followed what Mr. Nat Wooding said, saved the excel file as a csv
> > file,
> > then used proc import, and succeeded. I also called SAS help and they
> > said
> > it is a problem of Microsoft; Excel can not have more than 256 columns.>
> > Actually, Excel 2007 can handle 16,384 columns (256*64). Go figure.
> > Frank
> "Games? Solitaire? I have a 2-node VAXcluster, 2 Windows 2000 servers, 1
> Windows 2003 server, 1 MySQL server, 1 Linux server, several Ubuntus and
> a direct satellite feed to my windows desktop background, who needs
> toys???" - Jim