topic Re: Multiple variables from one column? in SAS Procedures

Multiple variables from one column?

JAR — Fri, 18 Apr 2014 14:57:07 GMT

Hi,

This is in continuation of an earlier discussion.

I have the following raw data:

Best software for Analytic?

SAS

Microsoft Excel

SPSS

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

Re: Multiple variables from one column?

data_null__ — Fri, 18 Apr 2014 15:27:41 GMT

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;

Re: Multiple variables from one column?

data_null__ — Fri, 18 Apr 2014 15:37:05 GMT

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;

Re: Multiple variables from one column?

Ksharp — Fri, 18 Apr 2014 16:14:46 GMT

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

Re: Multiple variables from one column?

ballardw — Fri, 18 Apr 2014 16:57:59 GMT

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.

Re: Multiple variables from one column?

JAR — Fri, 18 Apr 2014 17:30:11 GMT

Thanks a lot. It worked.

Re: Multiple variables from one column?

JAR — Fri, 18 Apr 2014 17:32:01 GMT

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

Re: Multiple variables from one column?

JAR — Fri, 18 Apr 2014 17:37:17 GMT

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.