topic Re: Transposing dataset using do loops and arrays. in SAS Programming

Transposing dataset using do loops and arrays.

sss — Tue, 15 Sep 2015 08:48:48 GMT

Hi All,

Used following code to create dataset Matrix dataset.

data Matrix(drop= i j);

array r(5);

do i=0 to 20 by 5;

do j=1 to 5;

r(j)=i+j;

end;

output;

end;

Below transpose code transpose the dataset as i required. but i would like to do same with dataset using do loops and arrays.

proc Transpose prefix=r out=Transpose(drop=_NAME_);

Below code could not give me same output as proc transpose. could you please help me to transpose dataset in datastep?

data Transpose2(drop= i j r1-r5 flag);

set Matrix;

flag=0;

array v(5);

array r(5);

do i=1 to 5;

do j=1 to 5;

_N_=j;

v(j)=r(i);

end;

run;

I am trying to read first column of data set Matrix and storing it as a first row in data set Transpose. I am using _N_ automatic variable to switch between rows of dataset matrix so that I can read all values of first column.

Here I read 1st value of 1st column(then 2nd value of 1st column & so on), and then switching to second row by setting the value of _N_to 2 (so that SAS will jump to 2^nd row in PDV.) and reading the 3rd value of 1st column & so on….. But it’s not working. Is this right way? Please give any hint about the logic or hint related to using array in different way.

Re: Transposing dataset using do loops and arrays.

RW9 — Tue, 15 Sep 2015 10:53:51 GMT

Sorry, your post is quite unclear to me. Please provide test data, in the form of a dataset, and what the output should look like.

Re: Transposing dataset using do loops and arrays.

Reeza — Tue, 15 Sep 2015 14:45:21 GMT

I don't think it makes sense to do this, except possibly as an exercise. If you want to work with Matrices its best to use SAS IML. Otherwise Proc Transpose is efficient.

Re: Transposing dataset using do loops and arrays.

osvaldoberg — Tue, 15 Sep 2015 15:45:20 GMT

Hey.

See that.

DATA FINAL_1;
	SET ORIGEM;

	BY KEY;

	IF FIRST.KEY THEN
		MAX_KEY = 1;
	ELSE
		MAX_KEY + 1;
RUN;

PROC SQL NOPRINT;
SELECT COMPRESS(PUT(MAX(MAX_KEY),2.)) INTO:MAX_REF FROM FINAL_1;
QUIT;

%PUT ***&MAX_REF***;

DATA 	WORK.FINAL_2;
			SET FINAL_1;
 	BY MAX_KEY;

 	ARRAY TRANSPOSE     	{&MAX_REF.}$30.			TRANSPOSE_01 - TRANSPOSE_&MAX_REF.;
	
	IF FIRST.KEY THEN 
	DO;
		J = 1;
		DO WHILE (J <= &MAX_REF.);
			VARIABLE_TO_TRANS[J]	= "";
			J = J + 1;
		END;
		DROP J;
	END;

	VARIABLE_TO_TRANS[MAX_KEY] = TRANSPOSE;
		
	RETAIN	TRANSPOSE;
	
	IF LAST.KEY THEN 
		OUTPUT WORK.FINAL_2;
RUN;

Thanks.

Re: Transposing dataset using do loops and arrays.

MikeZdeb — Tue, 15 Sep 2015 18:30:52 GMT

Hi. FYI and just a suggestion.

You don't need the COMPRESS and PUT functions when creating the macro variable in PROC SQL. There is no data type for a macro variable value (it's just text) so the PUT isn't needed. The TRIMMED option will get rid of all the leading blanks.

For example ...

proc sql noprint;
select compress(put(max(age),2.)), max(age), max(age) into :max1, :max2, :max3 trimmed from sashelp.class;
quit;

%put |&max1|;
%put |&max2|;
%put |&max3|;

the LOG ...

376 %put |&max1|;
|16|
377 %put |&max2|;
| 16|
378 %put |&max3|;
|16|

Re: Transposing dataset using do loops and arrays.

MikeZdeb — Tue, 15 Sep 2015 18:58:46 GMT

Hi ...

data matrix;
array r(5);
do i=0 to 20 by 5;
do j=1 to 5;
r(j)=i+j;
end;
output;
end;
keep r: ;
run;

data new;
array r(5);
array v(5);
do i=1 to 5;
do j=1 to 5;
set matrix point=j;
v(j) = r(i);
end;
output;
end;
stop;
keep v: ;
run;

data set NEW ...

Obs v1 v2 v3 v4 v5

1 1 6 11 16 21
2 2 7 12 17 22
3 3 8 13 18 23
4 4 9 14 19 24
5 5 10 15 20 25

If you wanted to make the transpose data step general, you could try ...

proc sql noprint;
select nvar, nlobs into :nvars trimmed, :nobs trimmed from dictionary.tables
where libname = 'WORK' and memname = 'MATRIX';
quit;

data new;
array r(&nvars);
array v(&nobs);
do i=1 to &nvars;
do j=1 to &nobs;
set matrix point=j;
v(j) = r(i);
end;
output;
end;
stop;
keep v: ;
run;