topic Re: Transpose data from colum to ROWS by DOG in SAS Programming

Transpose data from colum to ROWS by DOG

jonatan_velarde — Tue, 01 Oct 2019 00:05:58 GMT

hi SAS colleages:

i have this data set:

data have;

input DOG new_age weight_at_new_age;

cards;
1 . .
1 20 5.03214
1 40 9.67500
1 60 16.26786
1 80 21.91071
1 100 21.55357
1 120 29.57500
1 140 33.99643
1 160 32.45484
1 180 35.55161
1 200 39.22000
2 20 7.17826
2 40 11.70714
2 60 15.48929
2 80 18.18571
2 100 21.90000
2 120 24.03571
2 140 25.76786
2 160 33.00968
2 180 36.41600
2 200 36.33333
2 220 37.66667
2 240 39.00000
2 260 40.33333
2 280 41.66667
2 300 43.00000
2 320 43.07857
2 340 43.16071
3 20 5.94348
3 40 10.57037
3 60 14.62857
3 80 17.40000
3 100 19.40000
3 120 22.50000
3 140 25.64286
3 160 27.78571
3 180 28.81818
3 200 30.84242
3 220 33.00690
3 240 34.38621
3 260 36.10000
3 280 37.18571
3 300 36.47143
3 320 37.81429
3 340 39.24286
3 360 40.21429
3 380 38.35714
3 400 37.60000
3 420 37.35714
3 440 37.21429
3 460 37.32857
3 480 35.65000
3 500 36.52143
3 520 40.73571
3 540 41.20000
3 560 41.02069
3 780 63.77931
3 800 65.15000
4 20 6.97826
4 40 9.20000
4 60 11.59286
4 80 14.37857
4 100 17.16429
4 120 19.75000
4 140 22.25000

;

i need to obtain is:

DOG	0	20	40	60	80	100	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580	600	620	640	660	680	700	720	740	760	780	800
1	3.5000	5.0321	9.6750	16.2679	21.9107	21.5536	29.5750	33.9964	32.4548	35.5516	39.2200	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
2	3.7000	7.1783	11.7071	15.4893	18.1857	21.9000	24.0357	25.7679	33.0097	36.4160	34.7880	37.7480	40.7080	43.6680	46.6280	49.5880	43.0786	43.1607	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
3	4.9000	5.9435	10.5704	14.6286	17.4000	19.4000	22.5000	25.6429	27.7857	28.8182	30.8424	33.0069	34.3862	36.1000	37.1857	36.4714	37.8143	39.2429	40.2143	38.3571	37.6000	37.3571	37.2143	37.3286	35.6500	36.5214	40.7357	41.2000	41.0207	.	.	.	.	.	.	.	.	.	.	63.7793	65.1500
4	3.5000	6.9783	9.2000	11.5929	14.3786	17.1643	19.7500	22.2500	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.

Thank you very much

Re: Transpose data from colum to ROWS by DOG

PaigeMiller — Tue, 01 Oct 2019 00:23:19 GMT

PROC Transpose with a BY statement and a ID statement ought to work, except that SAS variable names cannot be numbers, as in your shown desired output. If you are willing to have the label, not the variable name, be the number, then the IDLABEL statement ought to work. Example: https://documentation.sas.com/?docsetId=proc&docsetVersion=9.4&docsetTarget=n1t09lqx9e277en16e70gu36q1i2.htm&locale=en

Re: Transpose data from colum to ROWS by DOG

sustagens — Tue, 01 Oct 2019 01:07:21 GMT

No need to sort this time because your variables are already sorted, but don't forget to include a proc sort prior to transposing.

What I do as an alternative to labels with regards to numeric column names is add an underscore as a prefix.

PROC TRANSPOSE DATA=have
	OUT=want 
	PREFIX=_
;
	BY DOG;
	ID new_age;
	VAR weight_at_new_age;
RUN; 
QUIT;

Re: Transpose data from colum to ROWS by DOG

PaigeMiller — Tue, 01 Oct 2019 01:15:31 GMT

That's a good idea as well