topic Transpose Columns into Rows and vice versa with an array in SAS Programming

Transpose Columns into Rows and vice versa with an array

KarimaTouati — Mon, 06 Jan 2020 14:39:15 GMT

I have the following data. Table1 which is symetric and the number of rows is equal to the numbers of columns.

I want to make a transpose, so columns become rows and vice versa without using the Transpose function since I don't have IML Licence.

Can you help me please ?

Re: Transpose Columns into Rows and vice versa with an array

Rick_SAS — Mon, 06 Jan 2020 14:49:30 GMT

Here is the conversion from "wide format" (many columns) to "long format":

data Have;
input Annee Annee1-Annee7;
datalines;
1995 1 2 3 4 5 6 7
1996 1 2 3 4 5 6 .
1997 1 2 3 4 5 . .
1998 1 2 3 4 . . .
1999 1 2 3 . . . .
2000 1 2 . . . . .
2001 1 . . . . . .
;

data Want;
set Have;
array Y[7] Annee1-Annee7;
i = _N_;
do j = 1 to dim(Y) - i + 1;
   Value = Y[j];
   output;
end;
keep Annee i j Value;
run;

I'll bet that if you think about it you will be able to go the other way without help. Give it a try!

Re: Transpose Columns into Rows and vice versa with an array

FreelanceReinh — Mon, 06 Jan 2020 14:49:32 GMT

Hello @KarimaTouati and welcome to the SAS Support Communities!

The TRANSPOSE procedure does exactly what you describe:

proc transpose data=have out=want;
run;

Re: Transpose Columns into Rows and vice versa with an array

ed_sas_member — Mon, 06 Jan 2020 14:54:48 GMT

Hi @KarimaTouati

In addition to @FreelanceReinh's answer, I would also add a COPY statement not to transpose the "annee" column:

data have;
	infile datalines truncover;
	input annee annee1-annee7;
	datalines;
1995 23758 49114 68582 79840 86298 90566 92878
1996 31245 63741 90775 106439 115054 120210
1997 26312 57779 82451 95506 101664
1998 30470 65482 90973 103562
1999 49756 101587 136854
2000 50420 102735
2001 56762
;
run;
proc transpose data=have out=want (drop=_name_);
copy annee;
run;

Re: Transpose Columns into Rows and vice versa with an array

jhammouda — Mon, 06 Jan 2020 15:32:05 GMT

Hi Karima,

I do not know if you are aware of this, but there is a university version of SAS available for free, that will give you the transpose functionality. Here is the link.

Good Luck

https://www.sas.com/en_us/software/university-edition.html