I have a data entry system for a survey done in cancer research. Most of the fields are character fields. We use character fields to store coded responses like "1" no relatives have had cancer "2" some relatives have had cancer. If for some reason the respondent skips entry in a field we need to know so we record "s" for skipped. Skipped means the question wasn't asked because it wasn't relavent. For instance we skip questions about menopause when the respondent is male. :)

Sometimes a respondent independently decides to leave a question blank. Those are recorded as "m" for missing since we expected them to answer ... they just didn't.

This all works fine as long as we work with the data in Fox. However we have to export the data for analysis by statistician in their analysis packages, the software of choice for analysis around here is SAS.

The analysts prefer to get everthing in integers so we convert the "s" values to .NULL. and the "m" values to 0.

open data c:\temp\cancer
sele 0
use cancer!responses

copy structure extended to [c:\temp\structuretable]
sele 0
use [c:\temp\structuretable]
replace field_type with "I", field_len with 4 for field_name = "CHADCANCERBEFORE"

create database c:\TEST\TESTDBC
open database "c:\test\TESTDBC"
create [c:\temp\newtable] from [c:\temp\structuretable]

The code above creates a table in the correct structure and then it has been no problem to convert the data.

The Problem: Long field names get lost.

create <table> from <structure>

I can't figure out how to use this command to direct a newly created table into a database. I looked for a database clause but couldn't find one.

Here is what I did:

use in newtable
set database to testdbc
add table "c:\temp\newtable"
use testdbc!newtable in 0
afields(lafields,[newtable])
use [c:\temp\structuretable] in 0
for i = 1 to alen(lafields,1)
  go i in structuretable
  lcfield = lower(lafields(i,1))
  lcnewfield = lower(alltrim(assessjk.field_name))
  if not(lcfield == lcnewfield)
    * Only alter the table if the newfield is not the same
    alter table assesssas rename column &lcfield to &lcnewfield
  endif
endfor

Anyway this seems too complex.

What am I not seeing?

Carole Shaw
Fred Hutchinson Cancer Research Center

Eagles may soar, but weasels don't get sucked into jet engines.