Help using Base SAS procedures

Multiple variables from one column?

Accepted Solution Solved
Reply
Contributor JAR
Contributor
Posts: 45
Accepted Solution

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.

QuestionABCD
Best software for Analytics?SASMicrosoft ExcelSPSSR
Capital of Malvi?LondonLilongeweParis

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
Respected Advisor
Posts: 3,799

Re: Multiple variables from one column?

Posted in reply to data_null__

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

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
Respected Advisor
Posts: 3,799

Re: Multiple variables from one column?

Posted in reply to data_null__

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 JAR
Contributor
Posts: 45

Re: Multiple variables from one column?

Posted in reply to data_null__

Thanks a lot. It worked.

Super User
Posts: 10,044

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 JAR
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: 11,343

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 JAR
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 234 views
  • 4 likes
  • 4 in conversation