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)?
Thank you Anca! The code is working. There is no year attached to this date, that is the reason you cannot see the YEAR 🙂
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.
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 |
And what does your input data look like, and the complete PROC TRANSPOSE code plz.
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.
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.
You both are correct! I need to change this date format!
THANKS FOR YOUR HELP!
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.
Thank you Anca! The code is working. There is no year attached to this date, that is the reason you cannot see the YEAR 🙂
yes, I don't understand the input data either, it is hard to read it in.
but I am glad some code works
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;
Umm I am not sure,
can you provide a simple output that you'd like?
Anca.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.