topic converting wide format data to long format in SAS Programming

converting wide format data to long format

Rammed — Fri, 18 May 2018 15:39:48 GMT

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

Re: converting wide format data to long format

HB — Fri, 18 May 2018 15:52:58 GMT

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/

Re: converting wide format data to long format

ballardw — Fri, 18 May 2018 15:55:01 GMT

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.

Re: converting wide format data to long format

novinosrin — Fri, 18 May 2018 16:14:59 GMT

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;

Re: converting wide format data to long format

Reeza — Fri, 18 May 2018 16:17:13 GMT

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.

Re: converting wide format data to long format

Rammed — Sat, 19 May 2018 05:15:35 GMT

Thanks for this easy way.

Re: converting wide format data to long format

Rammed — Sat, 19 May 2018 05:19:55 GMT

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