DATA Step, Macro, Functions and more

converting wide format data to long format

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

converting wide format data to long format

Hi All,

 

I am pretty new to SAS and would like to change the format of my data in a below way.

I could not understand how the available codes work., so your help will be much appreciated. 

 

obs.  var1 var2 var3 var4 var5

1        3      4      4      3      2

2        4      1      2      4      5

3        5      7      8      9      6 

4        1      9      3      6      4

 

I want the long dataset in way that each observation will have two rows. var 1 to 3 are the same for both rows. The value of var4 will come in first row and the value of var 5 will be in the second row of each observation.Then  a new variable should be created showing 0 where var4 and 1 where var 5. So the long dataset should be like below:

 

obs.  var1 var2 var3    var4-5     new_var

1        3      4      4          3              0

1        3      4      4          2              1

2        4      1      2          4              0

2        4      1      2          5              1

3        5      7      8          9              0

3        5      7      8          6              1

4        1      9      3          6              0

4        1      9      3          4              1

 

Thanks a lot

Rama

 

 


Accepted Solutions
Solution
‎05-19-2018 01:14 AM
Super User
Posts: 23,323

Re: converting wide format data to long format

Posted in reply to novinosrin
data want;
set have;

var4_5=var4;  new_var=0;
output;
var4_5=var5;  new_var=1;
output;

drop var4 var5;
run;

Don't really need the array for such a simple problem. 

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 266

Re: converting wide format data to long format

Why on earth would you want to do this?

 

Maybe you could tell us the problem you are really trying to solve, I don't think this is it.  I'm guessing you think this data reorg will solve another problem we don't know about.  My guess is this data reorganization is not the solution to that other problem. 

 

http://xyproblem.info/

 

 

New Contributor
Posts: 4

Re: converting wide format data to long format

You are right. Actually I am doing difference-in-differences model. There are two ways to do that: one using Proc Glm that can be performed with wide-format data. another way is using proc mixed that needs long format of data. I knew that the results should be similar. However, because i did not get good result with proc glm, I though just in case is good to try proc glm, that is why I asked for that.
Thanks
Super User
Posts: 13,338

Re: converting wide format data to long format

Step 1: Provide data in the form of a data step. As it is we do not know if OBS is actually a variable in your data. I am going to assume not.

data have;
  input var1 var2 var3 var4 var5;
datalines;
 3      4      4      3      2
 4      1      2      4      5
 5      7      8      9      6 
 1      9      3      6      4
;
run;

since you want to do sort of count may as well use a data step:

 

data want;
   set have;
   array v var4 var5;
   do i= 1 to dim(v);
      var4_5= v[i];
      new_var= i-1;
      output;
   end;
   drop var4 var5 i;
run

 

Note that if you have more variable to string out (var4 and var5) adding them to the array and drop statement this approach would count each added variable as well.

PROC Star
Posts: 1,592

Re: converting wide format data to long format

data have;
  input var1 var2 var3 var4 var5;
datalines;
 3      4      4      3      2
 4      1      2      4      5
 5      7      8      9      6 
 1      9      3      6      4
;
run;

data want;
set have;
array t(*) var:;
var4_5=t(4);
new_var=0;
output;
var4_5=t(5);
new_var=1;
output;
drop var4 var5;
run;
Solution
‎05-19-2018 01:14 AM
Super User
Posts: 23,323

Re: converting wide format data to long format

Posted in reply to novinosrin
data want;
set have;

var4_5=var4;  new_var=0;
output;
var4_5=var5;  new_var=1;
output;

drop var4 var5;
run;

Don't really need the array for such a simple problem. 

New Contributor
Posts: 4

Re: converting wide format data to long format

Thanks for this easy way.
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 317 views
  • 9 likes
  • 5 in conversation