topic Re: Creating wide table to narrow table having two groups of data elements in SAS Programming

Creating wide table to narrow table having two groups of data elements

DeepakSwain — Tue, 16 Mar 2021 17:17:42 GMT

Hi there,

I need your kind help to transpose my table having 2 groups of data elements related to dates and weight.

data have;
id= 101;
date_1= '20200101' ;
weight_1=51;
date_2='20200102';
weight_2=52;
date_3='20200103';
weight_3=53;
date_4='20200104';
weight_4=54;
output;
id= 102;
date_1= '20200101' ;
weight_1=61;
date_2='20200102';
weight_2=62;
date_3='20200103';
weight_3=63;
date_4=' ';
weight_4=.;
output;
id= 103;
date_1= '20200101' ;
weight_1=71;
date_2='20200102';
weight_2=72;
date_3=' ';
weight_3=. ;
date_4='20200104 ';
weight_4=74 ;
output;
run;




data want;
input id  date $ weight ;
datalines;
101 20200101 51
101 20200102 52
101 20200103 53
101 20200104 54
102 20200101 61
102 20200102 62
102 20200103 63
103 20200101 71
103 20200102 72
103 20200104 74
;
run;

Thank you in advance for your kind reply.

Re: Creating wide table to narrow table having two groups of data elements

ballardw — Tue, 16 Mar 2021 17:25:15 GMT

Why do you have "dates" as character values???

One way:

data want;
   set have;
   by id;
   array d (*) date_1-date_4;
   array w (*) weight_1-weight_4;
   do i=1 to dim(d);
      date=d[i];
      weight=w[i];
      output;
   end;
   keep id date weight;
run;

Character dates are pretty hard to work with for any manipulation and they make lousy graph axis values.

Re: Creating wide table to narrow table having two groups of data elements

DeepakSwain — Tue, 16 Mar 2021 17:37:44 GMT

Thanks a lot for your quick reply. I agree with you regarding character form of date. To improve the efficiency, I will convert it to date format.

Re: Creating wide table to narrow table having two groups of data elements

ballardw — Tue, 16 Mar 2021 17:53:39 GMT

@DeepakSwain wrote:
Thanks a lot for your quick reply. I agree with you regarding character form of date. To improve the efficiency, I will convert it to date format.

We can actually do that when the shape is changed.

data want;
   set have;
   by id;
   array d (*) date_1-date_4;
   array w (*) weight_1-weight_4;
   do i=1 to dim(d);
      date= input(d[i],yymmdd8.);
      weight=w[i];
      output;
   end;
   format date yymmdd10. ;/* or format of your choosing*/
   keep id date weight;
run;