DATA Step, Macro, Functions and more

Splitting varialbes into multiple

Reply
Occasional Contributor den
Occasional Contributor
Posts: 16

Splitting varialbes into multiple

I'm a fairly novice SAS programmer and after doing multiple searches I'm no wiser as to how to solve my current problem.

My dataset is fairly simple. I have two variables, namely caseid and condition. An example of the data is given:

caseid condition
1 headache
1 cough
1 vomiting
2 headache
2 cough
3 vomiting
4 rash
4 anaemia

I would like to split the "condition" variable into different variables based on the list of coniditons and so that each row within the data is listed by the caseid and a value of "1" is given when the caseid has the condition and a "0" when they don't. I would like my data to appears as:

caseid headache cough vomiting rash anaemia
1 1 1 1 0 0
2 1 1 0 0 0
3 0 0 1 0 0
4 0 0 0 1 1

I have tried transposing the data using caseid as the "by" variable which results in a dataset where the list of conditions still remains as the observation and not as a variable label. I've also tried creating an array to input the data into. All to no avail. I would appreciate any advice on how to resolve this issue.
Frequent Contributor
Posts: 120

Re: Splitting varialbes into multiple

Try this:

data test;
length caseid 8 condition $20;
input caseid condition;
datalines;
1 headache
1 cough
1 vomiting
2 headache
2 cough
3 vomiting
4 rash
4 anaemia
;
run;

data test1/view=test1;
set test;
length flag 8;
flag = 1;
run;

options missing = 0;
proc transpose data = test1 out = test2;
by caseid;
id condition;
var flag;
run;

//Fredrik
Super Contributor
Posts: 648

Re: Splitting varialbes into multiple

Try this:


data condition;
input caseid 1-2 condition $3-10;
datalines;
1 headache
1 cough
1 vomiting
2 headache
2 cough
3 vomiting
4 rash
4 anaemia
;
run;


proc report data=condition nowindows out=cond1(drop=_break_ rename=(_c2_=anaemia _c3_=cough _c4_=headache _c5_=rash _c6_=vomiting));
column caseid condition;
define caseid/group;
define condition/across order=internal;
run;
Respected Advisor
Posts: 3,777

Re: Splitting varialbes into multiple

You can turn those missing values into zeros with the PROC REPORT statement option COMPLETROWS. Now you just need a way to automatically rename the _Cn_ variables.

[pre]
proc report data=condition nowindows
completerows
out=cond1(drop=_break_ rename=(_c2_=anaemia
_c3_=cough _c4_=headache _c5_=rash _c6_=vomiting));
column caseid condition;
define caseid/group;
define condition/across order=internal;
run;
[pre]
SAS Super FREQ
Posts: 8,744

Re: Splitting varialbes into multiple

Hi:
"now you just need a way to automatically rename the _Cn_ variables" -- there's not a way within PROC REPORT to do that. The RENAME option on the OUT= is the way. So if you wanted "automatically" you'd have to do it with macro techniques.

cynthia
Respected Advisor
Posts: 3,777

Re: Splitting varialbes into multiple

Yes the rename is too tedious and not data driven. That's why I think TRANSPOSE is a better choice.

[pre]
data condition;
input caseid 1-2 condition $3-10;
datalines;
1 headache
1 cough
1 vomiting
2 headache
2 cough
3 vomiting
4 rash
4 anaemia
;;;;
run;

proc summary data=condition nway completetypes;
class caseid;
class condition / order=data;
output out=test2;
run;
proc transpose data=test2 out=test3(drop=_name_);
by caseid;
id condition;
var _freq_;
run;
proc print;
run;


Obs caseid headache cough vomiting rash anaemia

1 1 1 1 1 0 0
2 2 1 1 0 0 0
3 3 0 0 1 0 0
4 4 0 0 0 1 1

[/pre]
Super Contributor
Posts: 648

Re: Splitting varialbes into multiple

How do we order the variables alphabetically?
Respected Advisor
Posts: 3,777

Re: Splitting varialbes into multiple

> How do we order the variables alphabetically?
I used ORDER=DATA because that is order the OP had used in his NEED data set. If you remove ORDER=DATA from my program the variables will be alphabetical.
Occasional Contributor den
Occasional Contributor
Posts: 16

Re: Splitting varialbes into multiple

Many thanks to data _null_ for the response to my original question. This worked perfectly!
Ask a Question
Discussion stats
  • 8 replies
  • 227 views
  • 0 likes
  • 5 in conversation