Multiple variables from one column?

[JAR]

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

[data_null__]

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;

[data_null__ · Accepted solution]

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;

[JAR]

Thanks a lot. It worked.

[Ksharp]

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

[JAR]

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

[ballardw]

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.

[JAR]

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.