topic Re: Transposing using an array in SAS Programming

Transposing using an array

bonedog — Mon, 22 Jul 2019 20:20:32 GMT

Hi all,

I'm trying to transpose this set using version 9.4 so before and after are column headers and the datalines fall below it.

Before 300 350 190 400 244 321 330 250 190 160 260 240
After 290 331 200 395 240 300 332 242 185 158 256 220

Every method I have tried thus far hasn't been successful. With the version of the code I've been currently using I get the error "Array subscript out of range at line ___"

What the heck am I missing? My current version of the code is below.

DATA dietpair (keep = before after);
infile 'X:\SASDATA\cody_chap06_num06.dat';
input trt $ 1-6 subject1-subject12;
array weight (12) subject1-subject12;
do i = 1-12;
if trt = 'Before' then before = weight(i);
else if trt = 'After' then after = weight(i);
end;
RUN;

Re: Transposing using an array

Tom — Mon, 22 Jul 2019 20:29:20 GMT

Negative eleven is not going to be a valid index into your array. You need use integers from 1 to 12 instead.

do I=1 to 12;

Do you only want the value for subject 12? Or do you want to output all of them? You need an OUTPUT statement inside your DO loop.

data dietpair ;
  infile 'X:\SASDATA\cody_chap06_num06.dat';
  input trt $ 1-6 @ ;
  do subject=1 to 12;
     input weight @;
     output;
  end;
run;

proc sort; by subject trt; run;

Re: Transposing using an array

bonedog — Mon, 22 Jul 2019 20:26:38 GMT

I don't know where you're seeing negative 11 - this is already what I have in my code.

Re: Transposing using an array

Tom — Mon, 22 Jul 2019 20:30:09 GMT

one minus twelve is negative eleven.

Re: Transposing using an array

ballardw — Mon, 22 Jul 2019 20:37:42 GMT

@bonedog wrote:

I don't know where you're seeing negative 11 - this is already what I have in my code.

Iterated do loops are

do I = <startvalue> to <endvalue> <optionally> by byvalue;

Startvalue, endvalue and byvalue can be 1) literal values, 1 or 12, 2) variable names or 3) expressions that resolve to integers though the values and sign of byvalue need to matched carefully. Note the word is to, not a dash.

Re: Transposing using an array

bonedog — Mon, 22 Jul 2019 20:38:40 GMT

Tom,

Thanks for pointing out my error there. The fix has gotten me most of the way there.

This is my current code:

DATA dietpair (keep = before after);
	infile 'X:\SASDATA\cody_chap06_num06.dat';
	input trt $ 1-6 subject1-subject12;
	array weight (12) subject1-subject12;
	do i = 1 to 12;
	if trt = 'Before' then before = weight(i);
	else if trt = 'After' then after = weight(i);
	output;
	end;
RUN;

And this is the output I'm getting.

                             Obs    before    after

                               1      300        .
                               2      350        .
                               3      190        .
                               4      400        .
                               5      244        .
                               6      321        .
                               7      330        .
                               8      250        .
                               9      190        .
                              10      160        .
                              11      260        .
                              12      240        .
                              13        .      290
                              14        .      331
                              15        .      200
                              16        .      395
                              17        .      240
                              18        .      300
                              19        .      332
                              20        .      242
                              21        .      185
                              22        .      158
                              23        .      256
                              24        .      220

How can I make it so I only have 12 observations where the before and after values line up?

Re: Transposing using an array

novinosrin — Mon, 22 Jul 2019 20:42:51 GMT

Hello @bonedog Do you just have two records in your source data?

Re: Transposing using an array

Tom — Mon, 22 Jul 2019 20:43:33 GMT

Since the data is rotated you might need to read it into two arrays if you want to transpose it in one pass.

data want;
   input label1 $ x1-x12 / label2 $ y1-y12 ;
   array x [12];
   array y [12];
   do subject=1 to 12 ;
       if label1 = 'Before' and label2='After' then do;
          before=x[subject]; after=y[subject];
      end;
       else if label2 = 'Before' and label1='After' then do;
          before=y[subject]; after=x[subject];
      end;
      else do;
          put 'Invalid row labels. ' label1= label2= ;
          stop;
      end;
      output;
   end;
   keep subject before after ;
cards;
Before 300 350 190 400 244 321 330 250 190 160 260 240
After 290 331 200 395 240 300 332 242 185 158 256 220
;