Download
1 / 12
120 likes | 252 Views
Changing a file from being long to being wide*. September 17, 2010. A summary of SAS-L posts by Ian Whitlock and datanull. The File You Have. data have; input First_Name $ status $ x1 x2 y1 y2 (hour1 hour2) (:time.) year1 year2; format hour: hhmm5.; cards ;
E N D
Changing a file from being long to being wide* September 17, 2010 • A summary of SAS-L posts by • Ian Whitlock and datanull
The File You Have data have; input First_Name $ status $ x1 x2 y1 y2 (hour1 hour2) (:time.) year1 year2; format hour: hhmm5.; cards ; Josh no 1 2 3 4 19:10 19:12 2010 2010 Josh yes 1 2 3 4 19:10 19:12 2010 2010 Josh no 5 6 7 8 08:05 08:07 2010 2010 Josh yes 5 6 7 8 08:05 08:07 2010 2010 ;
The File You Need data need; input First_Name $ status $ x1_1 x2_1 y1_1 y2_1 (hour1_1 hour2_1) (:time.) year1_1 year2_1 x1_2 x2_2 y1_2 y2_2 (hour1_2 hour2_2) (:time.) year1_2 year2_2; format hour: hhmm5.; cards ; Josh no 1 2 3 4 19:10 19:12 2010 2010 5 6 7 8 08:05 08:07 2010 2010 Mary no 1 2 3 4 19:10 19:12 2010 2010 5 6 7 8 08:05 08:07 2010 2010 ;
A Proc Transpose Solution /* sort data */ proc sort data=have out=temp1; by First_Name status; run;
A Proc Transpose Solution /* sort data */ proc sort data=have out=temp1; by First_Name status; run; /* add sequence # to make */ /* one obs per by group */ data temp1; set temp1; by first_name status; if first.status then seq = 0; seq + 1; run;
A Proc Transpose Solution /* sort data */ proc sort data=have out=temp1; by First_Name status; run; /* add sequence # to make */ /* one obs per by group */ data temp1; set temp1; by first_name status; if first.status then seq = 0; seq + 1; run; /* transpose */ proc transpose data = temp1 out = temp2; by first_name status seq; var x1 x2 y1 y2 hour1 hour2 year1 year2; run;
A Proc Transpose Solution /* sort data */ proc sort data=have out=temp1; by First_Name status; run; /* add sequence # to make */ /* one obs per by group */ data temp1; set temp1; by first_name status; if first.status then seq = 0; seq + 1; run; /* transpose */ proc transpose data = temp1 out = temp2; by first_name status seq; var x1 x2 y1 y2 hour1 hour2 year1 year2; run; /* create names for */ /* final transpose */ data temp2; set temp2; _name_ = cats (_name_ , "_", seq); run;
A Proc Transpose Solution /* sort data */ proc sort data=have out=temp1; by First_Name status; run; /* add sequence # to make */ /* one obs per by group */ data temp1; set temp1; by first_name status; if first.status then seq = 0; seq + 1; run; /* transpose */ proc transpose data = temp1 out = temp2; by first_name status seq; var x1 x2 y1 y2 hour1 hour2 year1 year2; run; /* create names for */ /* final transpose */ data temp2; set temp2; _name_ = cats (_name_ , "_", seq); run; /* produce desired file */ proc transpose data = temp2 out = need (drop=_:); by first_name status; var col1; run;
A Proc Transpose Solution /* sort data */ proc sort data=have out=temp1; by First_Name status; run; /* add sequence # to make */ /* one obs per by group */ data temp1; set temp1; by first_name status; if first.status then seq = 0; seq + 1; run; /* transpose */ proc transpose data = temp1 out = temp2; by first_name status seq; var x1 x2 y1 y2 hour1 hour2 year1 year2; run; /* create names for */ /* final transpose */ data temp2; set temp2; _name_ = cats (_name_ , "_", seq); run; /* produce desired file */ proc transpose data = temp2 out = need (drop=_:); by first_name status; var col1; run; /* reintroduce formats */ data need; set need; format hour: hhmm5.; run;
A Proc Summary Solution proc summary data=have nway; class first_name status; output out=need (drop=_:) idgroup(out[2](x1--year2)=); run;
