topic Re: Importing ACCESS table with Multiple response values in SAS Programming

Importing ACCESS table with Multiple response values

WKC — Fri, 13 May 2011 20:19:27 GMT

Hi, I have a problem when importing data from ACCESS table which takes multiple values.

One of the questions in the access form was:
What is (are) your favorite fruit? The coding of the answer are 1=Apple, 2=Orange, 3=Banana, 4=Melon, 5=Mango, 6=Strawberry. Respondents are allowed to choose all that applies, so if they pick more than one choice the data will be stored as 3,4,6 for example. However when I imported the database to SAS format, it only takes the first value (‘3’) but not whole value string (‘3,4,6’).

Please suggest what I need to do to fix it.

PROC IMPORT OUT= WORK.FruitData
DATATABLE= "Fruit Survey"
DBMS=ACCESS REPLACE;
DATABASE="&path\Fruit Survey.accdb";
SCANMEMO=YES;
USEDATE=NO;
SCANTIME=YES;
RUN;

PS. I am using windows SAS 9.2 TS2M3.

Thanks!

Re: Importing ACCESS table with Multiple response values

Reeza — Mon, 16 May 2011 23:25:20 GMT

I tried and created an access db with only 6 records and it works fine with either proc import or the libname statement, also SAS 9.2 TS2M3. I do notice that your extension is accdb though and not sure what the difference is, mine is mdb.

Two possibilities, check how SAS is displaying this variable, it may have read it in right, but have the wrong format set.

Or if the first time this happens is later in the dataset and you might need to play with the guessing rows options similar to excel.

HTH,
Reeza

Re: Importing ACCESS table with Multiple response values

WKC — Tue, 17 May 2011 15:37:08 GMT

I exported the database to a text file and found that the data was stored in a text field as "3;4;6”.

Not sure if that matters, but the answers were associated with another table which store the underlying coding ; ie. when one select Banana, Melon and Strawberry in the form, it will be stored as “3;4;6” as above.

SAS was able to read it as Character field and displayed as a single digit character (length=3 format=$3. Informat=$3.) in the SAS viewer. However the PROC FREQ procedure suggested the frequency as follow (the layout is edited to fit the display on this forum). :

Q2ID --- Frequency --- Percent --- Cumulative Frequency --- Cumulative Percent
---------------------------------------------------------------------------------------
3 0 --- 3 --- 0.53 --- 165 --- 29.36
3 1 --- 1 --- 0.18 --- 166 --- 29.54
3 2 --- 2 --- 0.36 --- 168 --- 29.89

I was confused, b/c in the dataset, I was not able to find anything like 3 0, 3 1 or 3 2 ...