topic Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var . in SAS Programming

Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

Chikku — Wed, 23 Mar 2016 10:59:33 GMT

Hi All,

Please find below the the input dataset containing 1 observation with 16 var.

which i want to convert as the output dataset having 4 obs with 4 var (4x4 format).

could you please let me know the logic that i can use to do it programatically.

Input dataset
res1	res2	res3	res4	res5	res6	res7	res8	res9	res10	res11	res12	res13	res14	res15	res16
1	0.866025	0.57735	0.75	0.866025	1	0.333333	0.57735	0.57735	0.333333	1	0.866025	0.75	0.57735	0.866025	1

output dataset to be generated:-

prod1	prod2	prod3	prod4
1	0.866025	0.57735	0.75
0.866025404	1	0.333333	0.57735
0.577350269	0.333333	1	0.866025
0.75	0.57735	0.866025	1

regards,

karthik.

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

RW9 — Wed, 23 Mar 2016 11:16:42 GMT

This should work:

data have;
 input res1	res2	res3	res4	res5	res6	res7	res8	res9	res10	res11	res12	res13	res14	res15	res16;
datalines;
1	0.866025	0.57735	0.75	0.866025	1	0.333333	0.57735	0.57735	0.333333	1	0.866025	0.75	0.57735	0.866025	1
;
run;

data want (keep=prod:);
  set have;
  array res{16};
  array prod{4} 8;
  do i=0 to 3;
    do j=1 to 4;
      prod{j}=res{(i*4)+j};
    end;
    output;
  end;
run;

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

FreelanceReinh — Wed, 23 Mar 2016 11:26:00 GMT

A slight modification of RW9's code would use a single DO loop rather than two nested DO loops:

do i=1 to 16;
  prod{mod(i-1,4)+1}=res{i};
  if ~mod(i,4) then output;
end;

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

Chikku — Wed, 23 Mar 2016 11:44:54 GMT

cou;d not see the complete code,can you resend.

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

RW9 — Wed, 23 Mar 2016 11:54:40 GMT

Its all there, in the grey box middle of the post?

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

data_null__ — Wed, 23 Mar 2016 13:58:19 GMT

It looks like a correlation matrix to me. You can alternatively put RES1-RES16 into a two dimensional array. I added some of the compenents of a TYPE=CORR data set _TYPE_ _NAME_.

data have;
   input res1-res16;
   datalines;
1  0.866025 0.57735  0.75  
0.866025 1  0.333333 0.57735
0.57735  0.333333 1  0.866025 
0.75  4.57735  0.866025 1
;;;;
   run;

data _2d(type=corr);
   retain _type_ 'CORR';
   length _name_ $32;
   set have;
   array res[4,4] res:;
   array prod[4];
   keep prod: _type_ _name_;;
   do i=1 to hbound(res,1);
      _name_ = vname(prod[i]);
      do j=1 to hbound(res,2);
         prod[j]=res[i,j];
         end;
      output;
      end;
   run;

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

ballardw — Wed, 23 Mar 2016 14:39:19 GMT

Repostin likely won't help. Internet explorer has problems rendering code from this forum that includes braces. Often a line or more of code will be missing after {

Re: Dataset Transformation from 1 observation with 16 var to dataset having 4 obs with 4 var .

Ksharp — Thu, 24 Mar 2016 07:36:07 GMT

It is IML thing.
1)

data have;
   input res1-res16;
   datalines;
1  0.866025 0.57735  0.75  
0.866025 1  0.333333 0.57735
0.57735  0.333333 1  0.866025 
0.75  4.57735  0.866025 1
;;;;
   run;
proc iml;
use have;
read all var _num_ into x;
close;
y=shape(x,4);
create want from y;
append from y;
close;
quit;








2)

data want;
 set have;
 array x{*} _numeric_;
 do i=1 to dim(x) by 4;
  pred1=x{i};pred2=x{i+1};pred3=x{i+2};pred4=x{i+3};
  output;
 end;
 keep pred:;
run;