Solved
Contributor
Posts: 45

# Multiple variables from one column?

Hi,

This is in continuation of an earlier discussion.

I have the following raw data:

Best software for Analytic?

SAS

Microsoft Excel

SPSS

R

Capital of Malvi?

London

Lilongewe

Paris

Which are questions and multiple answers for each question.

I would like to generate the following dataset.

 Question A B C D Best software for Analytics? SAS Microsoft Excel SPSS R Capital of Malvi? London Lilongewe Paris

Note 1: The empty space after "R" suggest the next entry is a question

Note 2: Questions are strings with space in it

Note 3: Multiple answers may have 2 or 3 or 4 items

Any suggestions will be greatly appreciated.

Jijil Ramakrishnan

Accepted Solutions
Solution
‎04-18-2014 11:37 AM
Posts: 3,852

## Re: Multiple variables from one column?

Oops.

data q;
infile cards eof=eof;
length question \$80.;

array x[4] \$40 a b c d;
input question \$80. field \$40.;

do _n_ = 1 by 1 while(not missing(field));
x[_n_] = field;

input field \$40.;

end;

output;

return;
eof:

output

stop;

drop field;
cards
Best software for Analytic?
SAS
Microsoft Excel
SPSS
R

Capital of Malvi?
London
Lilongewe
Paris
;;;;
run;
proc print;

run;

All Replies
Posts: 3,852

## Re: Multiple variables from one column?

Should I assume you are reading from and external file?

data q;
infile cards eof=eof;
input question \$80. / response \$40.;

do while(not missing(response));
output;

input response \$40.;

end;

return;
eof:

stop;

cards
Best software for Analytic?
SAS
Microsoft Excel
SPSS
R

Capital of Malvi?
London
Lilongewe
Paris
;;;;
run;
proc print;

run;
Solution
‎04-18-2014 11:37 AM
Posts: 3,852

## Re: Multiple variables from one column?

Oops.

data q;
infile cards eof=eof;
length question \$80.;

array x[4] \$40 a b c d;
input question \$80. field \$40.;

do _n_ = 1 by 1 while(not missing(field));
x[_n_] = field;

input field \$40.;

end;

output;

return;
eof:

output

stop;

drop field;
cards
Best software for Analytic?
SAS
Microsoft Excel
SPSS
R

Capital of Malvi?
London
Lilongewe
Paris
;;;;
run;
proc print;

run;
Contributor
Posts: 45

## Re: Multiple variables from one column?

Thanks a lot. It worked.

Super User
Posts: 10,787

## Re: Multiple variables from one column?

```data q;
infile cards length=len;
input x \$varying200. len;
if missing(x) then do;group+1;delete;end;
cards;
Best software for Analytic?
SAS
Microsoft Excel
SPSS
R

Capital of Malvi?
London
Lilongewe
Paris
;;;;
run;
proc transpose data=q out=want(drop=group _name_);
by group;
var x;
run;

```

Xia Keshan

Contributor
Posts: 45

## Re: Multiple variables from one column?

Thanks, this not only helps but this seems easier to program (but data_null's was faster) also. Thanks a lot... Jijil Ramakrishnan

Super User
Posts: 13,583

## Re: Multiple variables from one column?

Note 2 is incorrect with your data example. Microsoft Excel has a space but is not a question. I would suspect that a question mark would be a better indicator of a question but depending on how the data was entered I wouldn't bet on it.

Contributor
Posts: 45

## Re: Multiple variables from one column?

Both Null's and KSharp's code took "Microsoft Excel" as a single observation, and generated the output as I hoped it would. I thank you too for the thought.

🔒 This topic is solved and locked.