topic Re: Proc Transpose in SAS Procedures

Proc Transpose

Guinaba — Thu, 22 Aug 2013 12:06:42 GMT

When I use PROC TRANPOSE data=old

out=new_transp

name=col_tranposed

The col_transposed has character as format. How can I change to numeric (date)?

Re: Proc Transpose

AncaTilea — Thu, 22 Aug 2013 12:21:38 GMT

Hi.

It would helpful to provide a bit more detail.

But, the name = col_name it is simply you renaming the name as col_name.

OK, you want to have that variable be numeric, but that is not possible because the NAME variable contains the name of the variable you are transposing.

Ok, let me try to explain with an example (as you should have,too)

proc transpose data = sashelp.class out = test name = col_name;

var sex;

run;

If you run the code above, you'll see that all you've done is change the name of the variable _NAME_ to col_name, which is the same as running this code

proc transpose data = sashelp.class out = test;

var sex;

run;

The variable _NAME_ (or whatever you may want to call it) will always be character because it contains the information (name...) of the variable you are transposing.

ok.

So, maybe you can provide more details about what would you like?

Anca.

Re: Proc Transpose

Guinaba — Thu, 22 Aug 2013 12:51:42 GMT

Hi Anca,

Thank you! As you can see below after transposing my data, the column Period has character as format because it contains the information (name...) of the variable I am transposing...as you said. However, this column (Period) contains the time (days) information and I need to aggregate this info by week. That is the reason I would like to convert to numeric (date). Any suggestion how can I do that?

Period	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11
1/9	0	0	12	48	0	0	0	0	36	0	0
10/8	48	528	48	24	12	96	24	0	24	1725	210
10/9	24	312	192	300	120	132	120	60	228	735	150
11/8	0	0	24	48	12	0	0	0	36	0	0
11/9	36	372	192	192	48	60	144	24	168	960	75
12/8	36	372	180	192	36	108	72	36	132	960	105
12/9	36	180	180	300	36	72	120	48	168	1005	105
13/8	84	372	180	192	84	108	60	48	108	735	180
13/9	12	228	108	24	0	108	60	60	84	1140	135
14/8	24	552	144	156	12	24	84	12	180	915	105
14/9	24	492	96	48	0	84	48	0	60	1140	90
15/8	24	336	168	144	48	36	60	24	132	960	105
15/9	0	0	24	36	12	0	0	0	60	0	0
16/8	24	336	96	60	24	36	60	24	36	1080	225
16/9	24	180	144	144	0	84	60	24	180	660	135
17/8	0	528	24	12	24	72	48	0	24	1455	90
17/9	24	120	120	156	72	72	48	72	132	450	120
18/8	0	0	12	24	12	0	0	0	36	0	0
19/8	12	336	120	132	24	84	36	84	120	915	105
2/9	36	240	144	192	72	60	72	36	216	855	150
20/8	36	420	132	204	24	36	120	24	156	780	60

Re: Proc Transpose

LinusH — Thu, 22 Aug 2013 12:56:35 GMT

And what does your input data look like, and the complete PROC TRANSPOSE code plz.

Re: Proc Transpose

AncaTilea — Thu, 22 Aug 2013 13:03:04 GMT

Well given that you only provide pieces of information,

here is one way you could make your Period variable be a date:

data in;

input Period $ Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 Col11;

cards;

1/9 0 0 12 48 0 0 0 0 36 0 0

10/8 48 528 48 24 12 96 24 0 24 1725 210

10/9 24 312 192 300 120 132 120 60 228 735 150

11/8 0 0 24 48 12 0 0 0 36 0 0

11/9 36 372 192 192 48 60 144 24 168 960 75

12/8 36 372 180 192 36 108 72 36 132 960 105

12/9 36 180 180 300 36 72 120 48 168 1005 105

13/8 84 372 180 192 84 108 60 48 108 735 180

13/9 12 228 108 24 0 108 60 60 84 1140 135

14/8 24 552 144 156 12 24 84 12 180 915 105

14/9 24 492 96 48 0 84 48 0 60 1140 90

15/8 24 336 168 144 48 36 60 24 132 960 105

15/9 0 0 24 36 12 0 0 0 60 0 0

16/8 24 336 96 60 24 36 60 24 36 1080 225

16/9 24 180 144 144 0 84 60 24 180 660 135

17/8 0 528 24 12 24 72 48 0 24 1455 90

17/9 24 120 120 156 72 72 48 72 132 450 120

18/8 0 0 12 24 12 0 0 0 36 0 0

19/8 12 336 120 132 24 84 36 84 120 915 105

2/9 36 240 144 192 72 60 72 36 216 855 150

20/8 36 420 132 204 24 36 120 24 156 780 60

;

data want;

set in;

my_date = mdy(scan(period,2, "/"), scan(period, 1,"/"), 2013);

format my_date date9.;

run;

I obviously don't know what year is attached to these dates....

Anca.

Re: Proc Transpose

Guinaba — Thu, 22 Aug 2013 13:37:56 GMT

My input data looks like:

Total Stock - 4 Weeks Forecast	7/8	8/8	9/8	10/8	11/8	12/8	13/8	14/8	15/8	16/8	17/8	18/8	19/8	20/8	21/8	22/8	23/8	24/8	25/8	26/8	27/8	28/8	29/8	30/8	31/8	1/9	2/9	3/9	4/9	5/9	6/9	7/9	8/9	9/9	10/9	11/9	12/9	13/9	14/9	15/9	16/9	17/9
24	48	84	24	48	0	36	84	24	24	24	0	0	12	36	24	60	24	24	0	24	12	12	48	24	0	0	36	36	60	60	36	12	0	60	24	36	36	12	24	0	24	24
-5832	420	432	396	528	0	372	372	552	336	336	528	0	336	420	336	360	432	456	0	300	420	384	384	324	492	0	240	372	396	408	384	480	0	216	312	372	180	228	492	0	180	120
-1080	192	288	48	48	24	180	180	144	168	96	24	12	120	132	144	144	60	84	24	180	144	96	156	60	72	12	144	108	168	276	144	84	48	192	192	192	180	108	96	24	144	120
-696	180	348	108	24	48	192	192	156	144	60	12	24	132	204	120	132	60	24	60	108	180	180	168	72	36	48	192	144	144	180	60	12	96	336	300	192	300	24	48	36	144	156
252	48	48	24	12	12	36	84	12	48	24	24	12	24	24	60	36	24	0	0	36	24	36	72	12	12	0	72	24	0	48	36	12	0	36	120	48	36	0	0	12	0	72
-648	96	132	120	96	0	108	108	24	36	36	72	0	84	36	36	108	36	108	0	132	72	36	48	12	120	0	60	96	84	108	24	132	0	192	132	60	72	108	84	0	84	72
408	36	48	36	24	0	72	60	84	60	60	48	0	36	120	48	36	36	36	0	84	12	48	72	72	60	0	72	72	72	96	36	36	0	96	120	144	120	60	48	0	60	48
180	84	108	0	0	0	36	48	12	24	24	0	0	84	24	48	24	0	12	24	60	12	24	24	24	0	0	36	36	48	72	36	12	0	72	60	24	48	60	0	0	24	72
-1308	132	252	72	24	36	132	108	180	132	36	24	36	120	156	96	132	24	12	72	96	144	84	180	48	12	36	216	156	132	168	108	12	84	204	228	168	168	84	60	60	180	132
-11070	1350	1230	1350	1725	0	960	735	915	960	1080	1455	0	915	780	765	780	930	960	0	840	615	570	930	600	1170	0	855	795	930	855	1035	1200	0	1125	735	960	1005	1140	1140	0	660	450
-825	135	135	150	210	0	105	180	105	105	225	90	0	105	60	120	90	75	150	0	105	90	45	30	45	45	0	150	120	150	75	150	75	0	105	150	75	105	135	90	0	135	120
1680	195	120	240	315	0	180	165	120	135	195	240	0	180	165	150	105	150	150	0	135	120	90	45	120	105	0	180	105	150	120	60	165	0	285	165	105	150	75	195	0	225	165
-490	21	7	21	0	0	21	28	259	91	63	21	14	42	14	14	21	70	21	0	28	28	7	49	35	21	0	21	154	266	315	504	224	28	203	112	49	56	14	21	0	35	28
-392	70	63	49	21	63	119	91	693	231	105	63	91	154	189	84	224	203	133	119	91	105	133	98	105	77	91	189	609	980	1001	1211	1036	791	672	252	203	84	56	42	70	119	91
-1988	42	14	7	28	7	49	63	322	189	105	91	56	308	245	42	84	91	35	7	84	49	189	203	70	56	56	224	581	798	980	931	1029	189	427	224	105	91	56	28	21	98	98

and the PROC Transpose code is:

PROC TRANSPOSE DATA=DATA_01

OUT=DATA_02

PREFIX=Col

NAME=Period;

VAR Period;

I am not pasting all cols.

Period	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11
1/9	0	0	12	48	0	0	0	0	36	0	0
10/8	48	528	48	24	12	96	24	0	24	1725	210
10/9	24	312	192	300	120	132	120	60	228	735	150
11/8	0	0	24	48	12	0	0	0	36	0	0
11/9	36	372	192	192	48	60	144	24	168	960	75
12/8	36	372	180	192	36	108	72	36	132	960	105
12/9	36	180	180	300	36	72	120	48	168	1005	105
13/8	84	372	180	192	84	108	60	48	108	735	180
13/9	12	228	108	24	0	108	60	60	84	1140	135
14/8	24	552	144	156	12	24	84	12	180	915	105
14/9	24	492	96	48	0	84	48	0	60	1140	90
15/8	24	336	168	144	48	36	60	24	132	960	105
15/9	0	0	24	36	12	0	0	0	60	0	0
16/8	24	336	96	60	24	36	60	24	36	1080	225
16/9	24	180	144	144	0	84	60	24	180	660	135
17/8	0	528	24	12	24	72	48	0	24	1455	90
17/9	24	120	120	156	72	72	48	72	132	450	120
18/8	0	0	12	24	12	0	0	0	36	0	0
19/8	12	336	120	132	24	84	36	84	120	915	105
2/9	36	240	144	192	72	60	72	36	216	855	150
20/8	36	420	132	204	24	36	120	24	156	780	60
21/8	24	336	144	120	60	36	48	48	96	765	120
22/8	60	360	144	132	36	108	36	24	132	780	90
23/8	24	432	60	60	24	36	36	0	24	930	75
24/8	24	456	84	24	0	108	36	12	12	960	150
25/8	0	0	24	60	0	0	0	24	72	0	0
26/8	24	300	180	108	36	132	84	60	96	840	105
27/8	12	420	144	180	24	72	12	12	144	615	90
28/8	12	384	96	180	36	36	48	24	84	570	45
29/8	48	384	156	168	72	48	72	24	180	930	30
3/9	36	372	108	144	24	96	72	36	156	795	120
30/8	24	324	60	72	12	12	72	24	48	600	45
31/8	0	492	72	36	12	120	60	0	12	1170	45
4/9	60	396	168	144	0	84	72	48	132	930	150
5/9	60	408	276	180	48	108	96	72	168	855	75
6/9	36	384	144	60	36	24	36	36	108	1035	150
7/8	48	420	192	180	48	96	36	84	132	1350	135
7/9	12	480	84	12	12	132	36	12	12	1200	75
8/8	84	432	288	348	48	132	48	108	252	1230	135
8/9	0	0	48	96	0	0	0	0	84	0	0
9/9	60	216	192	336	36	192	96	72	204	1125	105

So now I need to aggregate the col Period in weeks.

Re: Proc Transpose

Guinaba — Thu, 22 Aug 2013 13:49:00 GMT

Thank you Anca! The code is working. There is no year attached to this date, that is the reason you cannot see the YEAR 🙂

Re: Proc Transpose

LinusH — Thu, 22 Aug 2013 13:54:55 GMT

Don't understand your input data, and that end up in your PROC TRANSPOSE...?

But anyway, as Anca said, you need to take care of the date somehow, make it SAS dates, so you could use SAS functions/formats to do some smart date manipulations.

Re: Proc Transpose

AncaTilea — Thu, 22 Aug 2013 14:07:27 GMT

yes, I don't understand the input data either, it is hard to read it in.

but I am glad some code works

Re: Proc Transpose

Guinaba — Thu, 22 Aug 2013 14:08:53 GMT

Hi Anca,

Just one last question, using your code how could I aggregate this data in a weekly interval?

data want;

set in;

my_date = mdy(scan(period,2, "/"), scan(period, 1,"/"), 2013);

format my_date date9.;

run;

Re: Proc Transpose

Guinaba — Thu, 22 Aug 2013 14:09:54 GMT

You both are correct! I need to change this date format!

THANKS FOR YOUR HELP!

Re: Proc Transpose

AncaTilea — Thu, 22 Aug 2013 14:26:00 GMT

Umm I am not sure,

can you provide a simple output that you'd like?

Anca.

Re: Proc Transpose

anushakalyani — Fri, 23 Aug 2013 06:52:06 GMT

Hi Guniba,

As per your earlier question from Anca's Code where aggregation is not given which is required but you said code is working. Have you added additional to Anca's code.Could you please let us know completely

Thanks,

Anusha

Re: Proc Transpose

Guinaba — Fri, 23 Aug 2013 10:12:57 GMT

Hi,

I have added the week function into Anca's code, after that, running a PROC MEANS (code below) I got the aggregate figures per week.

data Period2;

set Trans;

Date = mdy(scan(period,2, "/"), scan(period, 1,"/"),2013);

format Date date9.;

Week_= week(Date);

run;

PROC SORT Data= Period2;

BY Week_;

run;

PROC MEANS Data=Period2 sum;

var col1 - col697;

class Week_;

run;