BookmarkSubscribeRSS Feed
den
Calcite | Level 5 den
Calcite | Level 5
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.
8 REPLIES 8
FredrikE
Rhodochrosite | Level 12
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
SASPhile
Quartz | Level 8
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;
data_null__
Jade | Level 19
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]
Cynthia_sas
SAS Super FREQ
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
data_null__
Jade | Level 19
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]
SASPhile
Quartz | Level 8
How do we order the variables alphabetically?
data_null__
Jade | Level 19
> 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.
den
Calcite | Level 5 den
Calcite | Level 5
Many thanks to data _null_ for the response to my original question. This worked perfectly!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 956 views
  • 0 likes
  • 5 in conversation