DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 29

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

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.

Super User
Super User
Posts: 7,955

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

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;
Contributor
Posts: 29

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

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

Super User
Super User
Posts: 7,955

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

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

Super User
Posts: 11,343

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

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 {
Trusted Advisor
Posts: 1,117

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

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;
Respected Advisor
Posts: 3,799

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

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;

Capture.PNG

 

Super User
Posts: 10,028

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

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;

Ask a Question
Discussion stats
  • 7 replies
  • 277 views
  • 3 likes
  • 6 in conversation