topic How to use proc transpose in SAS Programming

How to use proc transpose

yotsuba88 — Tue, 19 Sep 2017 05:14:30 GMT

Hi,

I have this table

momr	std_res	sta_res	_rsq	id	skewret
	2.493	0.000	3.098	-0.085	0.478
1	5.324	-0.140	2.111	-0.041	0.283
2	4.694	-0.153	2.189	-0.053	0.317
3	5.122	-0.116	2.443	-0.062	0.429
4	5.763	-0.029	2.764	-0.058	0.444
5	6.116	-0.007	3.292	-0.057	0.352

I want to proc transpose to this:

	1	2	3	4	5	(5)-(1)
std_des	5.324	4.694	5.122	5.763	6.116	0.792 (49.55)
sta_des	-0.140	-0.153	-0.116	-0.029	-0.007	0.133 (9.55)
_rsq	2.111	2.189	2.443	2.764	3.292	1.181 (3.55)
ID	-0.041	-0.053	-0.062	-0.058	-0.057	-0.015 (6.55)
skewret	0.283	0.317	0.429	0.444	0.352	0.069

I run: proc transpose data=data out=want; var std_des sta_des _rsq id skewret;run;

The result with proc tranpose has only COL1, I have no idea to fix it and I want to make a new Column = Column 5 - Column1 and have t-stat in ().

Thank you so much for your help.

Re: How to use proc transpose

ChrisNZ — Tue, 19 Sep 2017 05:32:25 GMT

You can't have numbers as variable names.

And proc transpose will not do calculations,

Apart from that, this does what you want:


data HAVE;
  set SASHELP.CLASS(obs=5) ;
  COLNAME=catt('VAR_',_N_);
run;

proc transpose data=HAVE out=TMP; 
  var NAME SEX AGE HEIGHT WEIGHT;  
  id COLNAME; 
run;

data WANT;
  set TMP;
  DIFF=VAR_1-VAR_2;
run;

Re: How to use proc transpose

Tom — Tue, 19 Sep 2017 06:44:31 GMT

Seems pretty simple. Note that you will lose the rows (columns) where MOMR is missing.

data have ;
  input momr std_res sta_res _rsq id skewret;
cards;
. 2.493  0.000 3.098 -0.085 0.478
1 5.324 -0.140 2.111 -0.041 0.283
2 4.694 -0.153 2.189 -0.053 0.317
3 5.122 -0.116 2.443 -0.062 0.429
4 5.763 -0.029 2.764 -0.058 0.444
5 6.116 -0.007 3.292 -0.057 0.352
;
proc transpose data=have out=middle prefix=VAR  ;
  id momr ;
run;

data want ;
  set middle ;
  diff = var5 - var1;
run;
proc print;
run;

Obs    _NAME_      VAR1      VAR2      VAR3      VAR4      VAR5      diff

 1     std_res     5.324     4.694     5.122     5.763     6.116     0.792
 2     sta_res    -0.140    -0.153    -0.116    -0.029    -0.007     0.133
 3     _rsq        2.111     2.189     2.443     2.764     3.292     1.181
 4     id         -0.041    -0.053    -0.062    -0.058    -0.057    -0.016
 5     skewret     0.283     0.317     0.429     0.444     0.352     0.069