BookmarkSubscribeRSS Feed
WKC
Calcite | Level 5 WKC
Calcite | Level 5
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!
2 REPLIES 2
Reeza
Super User
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
WKC
Calcite | Level 5 WKC
Calcite | Level 5
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 ...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 716 views
  • 0 likes
  • 2 in conversation