Help using Base SAS procedures

Proc Transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Proc Transpose

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)?


Accepted Solutions
Solution
‎08-22-2013 09:49 AM
Occasional Contributor
Posts: 15

Re: Proc Transpose

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

View solution in original post


All Replies
Super Contributor
Posts: 543

Re: Proc Transpose

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?

Smiley Happy

Anca.

Occasional Contributor
Posts: 15

Re: Proc Transpose

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?

PeriodCol1Col2Col3Col4Col5Col6Col7Col8Col9Col10Col11
1/900124800003600
10/84852848241296240241725210
10/92431219230012013212060228735150
11/8002448120003600
11/93637219219248601442416896075
12/836372180192361087236132960105
12/9361801803003672120481681005105
13/884372180192841086048108735180
13/9122281082401086060841140135
14/82455214415612248412180915105
14/924492964808448060114090
15/82433616814448366024132960105
15/9002436120006000
16/824336966024366024361080225
16/9241801441440846024180660135
17/805282412247248024145590
17/92412012015672724872132450120
18/8001224120003600
19/81233612013224843684120915105
2/93624014419272607236216855150
20/83642013220424361202415678060
Super User
Posts: 5,257

Re: Proc Transpose

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

Data never sleeps
Occasional Contributor
Posts: 15

Re: Proc Transpose

My input data looks like:

Total Stock - 4 Weeks Forecast7/88/89/810/811/812/813/814/815/816/817/818/819/820/821/822/823/824/825/826/827/828/829/830/831/81/92/93/94/95/96/97/98/99/910/911/912/913/914/915/916/917/9
244884244803684242424001236246024240241212482400363660603612060243636122402424
-5832420432396528037237255233633652803364203363604324560300420384384324492024037239640838448002163123721802284920180120
-10801922884848241801801441689624121201321441446084241801449615660721214410816827614484481921921921801089624144120
-6961803481082448192192156144601224132204120132602460108180180168723648192144144180601296336300192300244836144156
25248482412123684124824241224246036240036243672121207224048361203612048360012072
-6489613212096010810824363672084363610836108013272364812120060968410824132019213260721088408472
4083648362407260846060480361204836363608412487272600727272963636096120144120604806048
18084108000364812242400842448240122460122424240036364872361207260244860002472
-13081322527224361321081801323624361201569613224127296144841804812362161561321681081284204228168168846060180132
-110701350123013501725096073591596010801455091578076578093096008406155709306001170085579593085510351200011257359601005114011400660450
-82513513515021001051801051052259001056012090751500105904530454501501201507515075010515075105135900135120
168019512024031501801651201351952400180165150105150150013512090451201050180105150120601650285165105150751950225165
-4902172100212825991632114421414217021028287493521021154266315504224282031124956142103528
-392706349216311991693231105639115418984224203133119911051339810577911896099801001121110367916722522038456427011991
-1988421472874963322189105915630824542849135784491892037056562245817989809311029189427224105915628219898

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.

PeriodCol1Col2Col3Col4Col5Col6Col7Col8Col9Col10Col11
1/900124800003600
10/84852848241296240241725210
10/92431219230012013212060228735150
11/8002448120003600
11/93637219219248601442416896075
12/836372180192361087236132960105
12/9361801803003672120481681005105
13/884372180192841086048108735180
13/9122281082401086060841140135
14/82455214415612248412180915105
14/924492964808448060114090
15/82433616814448366024132960105
15/9002436120006000
16/824336966024366024361080225
16/9241801441440846024180660135
17/805282412247248024145590
17/92412012015672724872132450120
18/8001224120003600
19/81233612013224843684120915105
2/93624014419272607236216855150
20/83642013220424361202415678060
21/8243361441206036484896765120
22/86036014413236108362413278090
23/824432606024363602493075
24/82445684240108361212960150
25/8002460000247200
26/82430018010836132846096840105
27/8124201441802472121214461590
28/81238496180363648248457045
29/8483841561687248722418093030
3/93637210814424967236156795120
30/8243246072121272244860045
31/8049272361212060012117045
4/9603961681440847248132930150
5/96040827618048108967216885575
6/93638414460362436361081035150
7/848420192180489636841321350135
7/912480841212132361212120075
8/88443228834848132481082521230135
8/900489600008400
9/9602161923363619296722041125105

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

Super User
Posts: 5,257

Re: Proc Transpose

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.

Data never sleeps
Occasional Contributor
Posts: 15

Re: Proc Transpose

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

THANKS FOR YOUR HELP!

Super Contributor
Posts: 543

Re: Proc Transpose

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....

Smiley Happy

Anca.

Solution
‎08-22-2013 09:49 AM
Occasional Contributor
Posts: 15

Re: Proc Transpose

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

Super Contributor
Posts: 543

Re: Proc Transpose

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

but I am glad some code works

Smiley Happy

Occasional Contributor
Posts: 15

Re: Proc Transpose

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;

Super Contributor
Posts: 543

Re: Proc Transpose

Umm I am not sure,

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

Anca.

Occasional Contributor
Posts: 15

Re: Proc Transpose

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;

New Contributor
Posts: 3

Re: Proc Transpose

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 440 views
  • 3 likes
  • 4 in conversation