N/A
Posts: 1

# HELP: Coding; cleaning data

I am very new to SAS. I was hoping someone would help we with a question. I have to do simple frequency diagrams for my professors survey. Anyhow, the excel data where all the answers are plugged in for a survey generate multiple answers for a question into 1 singular column rather than having seperate column for each answer for  example.

Question A; How many colors do you like

1. Red

2. Green

3. Yellow

4. Bue

and so on and so forth, so instead of having a sepearte column for red, and green and yellow. The raw data is coming out like this :

QA

COLUMN                                       COLUMN1      COLUMN2

User 1 : Red, Green                rather than     RED                GREEN

SAS when generating frequencies, will simply list all the answers into 1 column like the raw data, I need them to be seperated. Can someone help me out

Super Contributor
Posts: 644

## Re: HELP: Coding; cleaning data

The SCAN() function can be used to break up the composite response into separate responses.  You will need to know, or estimate, an upper bound to the number of possible individual responses to any question, and the maximum length of any individual reponse.  I'll assume 8 and 16. Also whether the comma is the only delimiter used.

Data recoded ;

set    survey ;

Array resp (8) \$16 resp1 - resp8 ;

Do k = 1 to 8 ;

resp(k) = left(scan(reponse, k, ',')) ;

End ;

Drop k ;

Run ;

Note - untested code.

Richard in Oz

PROC Star
Posts: 7,659

## Re: HELP: Coding; cleaning data

Another way to do it:

data have;

informat column \$30.;

input user column &;

cards;

1 Red, Green

2 Yellow, Blue, Red

3 Yellow, Blue, Red, Green

4 Red

5 Green

;

data need (drop=_;

set have (rename=(column=_column));

_i=1;

do while (scan(_column,_i) ne "");

column=scan(_column,_i);

_i+1;

output;

end;

run;

proc transpose data=need out=want;

var column;

by user;

run;

Discussion stats
• 2 replies
• 160 views
• 0 likes
• 3 in conversation