topic Re: Splitting varialbes into multiple in SAS Programming

Splitting varialbes into multiple

den — Fri, 01 Oct 2010 14:00:28 GMT

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.

Re: Splitting varialbes into multiple

FredrikE — Fri, 01 Oct 2010 14:25:35 GMT

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

Re: Splitting varialbes into multiple

SASPhile — Fri, 01 Oct 2010 14:37:43 GMT

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;

Re: Splitting varialbes into multiple

data_null__ — Fri, 01 Oct 2010 16:38:38 GMT

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]

Re: Splitting varialbes into multiple

Cynthia_sas — Fri, 01 Oct 2010 16:55:26 GMT

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

Re: Splitting varialbes into multiple

data_null__ — Fri, 01 Oct 2010 17:41:55 GMT

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]

Re: Splitting varialbes into multiple

SASPhile — Fri, 01 Oct 2010 19:14:13 GMT

How do we order the variables alphabetically?

Re: Splitting varialbes into multiple

data_null__ — Fri, 01 Oct 2010 20:57:59 GMT

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

Re: Splitting varialbes into multiple

den — Mon, 04 Oct 2010 11:57:51 GMT

Many thanks to data _null_ for the response to my original question. This worked perfectly!