Tranposing a Table!

[SMR]

Hi,

I am trying to tranpose a table (from Table 1 to Table 2) with ID from column to row, however keeping other data in column as illustrated below. Can any one pl. suggest how it can be done in SAS (using query or transpose task)? Many Thanks,
SAS user

Table 1 (from)

ID Date Time kWh
1 1-Jan-06 9 2
1 2-Jan-06 10 3
1 3-Jan-06 11 4
2 1-Jan-06 9 5
2 2-Jan-06 10 6
2 3-Jan-06 11 7
3 1-Jan-06 9 8
3 2-Jan-06 10 9
3 3-Jan-06 11 10

Table 2 (to this table)
ID
Date Time 1 2 3
1-Jan-06 9 2 5 8
2-Jan-06 10 3 6 9
3-Jan-06 11 4 7 10

[deleted_user]

I created and transposed your table 1 in Enterprise Guide by selecting Data>Transpose. Under Task roles, I selected the variable kWh for Transposed variables, id for New column names, date and time for Group analysis by. Under Options, I unchecked Use prefix. My results looked like your Table 2.

This is the SAS code for the transpose procedure that was generated.

PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
OUT=EGTASK.TRNSTransposedDATA(LABEL="Transposed HDSLIB.DATA")
NAME=Source
LABEL=Label
;
BY date time;
ID id;
VAR kwh;

[Olivier]

Be cautious as the previous solution works only because Enterprise Guide accepts variable names beginning with digits as default. This is because EG includes the following statement in all programs :
OPTIONS VALIDVARNAME=ANY ;

In SAS, you should never name variables as 1, 2, 3. But instead KWh1, KWh2, KWh3 would be just fine.
So prior the TRANSPOSE procedure, consider creating a new dataset with an extra variable, varName for example, with "KWh"!!id as a formula. Then use it as ID in the Transpose task.

Then the created dataset can be used anywhere, in any type of task.

Olivier

[deleted_user]

Oliver made a good point. If you run the code in SAS, not SAS Enterprise Guide the variable names will be _1 _2 and _3 unless you include the option to take any variable name which you would not do.

If you run it in SAS Enterprise Guide, just keep the include prefix checked and put the prefix as kWh to get kWh1, kWh2, etc. as the variable names.

Also, I noticed that I left off the sort procedure for the generated code and you need to sort on the BY variables or it will not work. I have included the entire code and included the PREFIX option on the Proc transpose.

Here it is.


data temp;
input ID Date date9. Time kWh;
format date date9.;
datalines;
1 1Jan06 9 2
1 2Jan06 10 3
1 3Jan06 11 4
2 1Jan06 9 5
2 2Jan06 10 6
2 3Jan06 11 7
3 1Jan06 9 8
3 2Jan06 10 9
3 3Jan06 11 10
run;
PROC SORT
DATA=WORK.TEMP(KEEP=kWh ID Date Time)
OUT=WORK.SORTTempTableSorted
;
BY Date Time;
RUN;

PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
OUT=Trans
NAME=Source
LABEL=Label
Prefix=kWh
;
BY date time;
ID id;
VAR kwh;
run;
proc print data=trans;
run;

[SMR]

Hi,

Many thanks Helen and Olivier. I used Helen's instruction for Enterprise Guide, and it did work for that simple table (I use only the guide at this moment and do not understand programming code yet).

I was actually struggling to create a table which is bit more complicated than the table that I provided before.

Is there a way to to create two rows of heading in SAS table outputs (as one can create in Excel)? I would like to convert Table 1 below to Table 2, for example.

I would like to have two rows of heading in Table 2, for example, one with No.1., No. 2, etc. And another row, under each of these No.1, No.2, with headings of dates (such as 1 Jan, 2Jan, 3Jan, etc). And my data will have columns of US$ for each of days (1Jan, 2Jan..) under No1, No.2, against time period.


Table1 (From this table)
ID Date Time(AM) US$
No. 1 1-Jan-06 9 200
No. 1 1-Jan-06 10 300
No. 1 1-Jan-06 11 450
No. 1 2-Jan-06 9 150
No. 1 2-Jan-06 10 160
No. 1 2-Jan-06 11 200
No. 1 3-Jan-06 9 144
No. 1 3-Jan-06 10 122
No. 1 3-Jan-06 11 500
No. 2 1-Jan-06 9 235
No. 2 1-Jan-06 10 214
No. 2 1-Jan-06 11 145
No. 2 2-Jan-06 9 254
No. 2 2-Jan-06 10 789
No. 2 2-Jan-06 11 100
No. 2 3-Jan-06 9 500
No. 2 3-Jan-06 10 650
No. 2 3-Jan-06 11 450


Table 2 (to this table)

Time(AM) No.1 No. 1 No. 1 No. 2 No. 2 No. 2
1-Jan-06 2-Jan-06 3-Jan-06 1-Jan-06 2-Jan-06 3-Jan-06
9 200 150 144 235 254 500
10 300 160 122 214 789 650
11 450 200 500 145 100 450

If one could create a unique column headings by picking names from from two columns. For example, to create a variable name 'No.1 1Jan', 'No1 2Jan' by combining No.1 and dates. Does it make sense?

Many thanks.

[deleted_user]

If I only wanted the results that you are talking about in a printed form, then I would use Proc Tabulate to produce a Summary Table. However, the column headings will look different from your description.
To produce a Summary Table in SAS Enterprise Guide, make your data table active by clicking on it. Then from the Menu bar, select Describe > Summary Tables.

Under Task roles make ID, Date, and Time the classification variables and US the analysis variable.

At this point, I wish I could just cut and paste what the Summary table layout looks like but I cannot.

So here goes the steps for the desired layout.
Select Summary Tables in the selection pane. Now you want to drag the variables under "Available variables" to the Preview area. Drag the variable Time to under the Box area to make it the Row Variable. Drag the variable US to the right of the box area to make it a column variable. Drag Sum (Sum of values) from under "Analysis Variables" to replace the N (the number of values). Because you only have one value for each ID, date, and time, the sum of the value works. Drag Date over the US variable. Drag ID over the Date variable. If you don't get it quit right, don't forget about the undo arrow on the right of the preview area or right click a cell and select "Remove cells".

Click run and see if that is what you want.

However, if you want it as a table similar to what you said above, here are the steps.

First of all, if you have not already entered the data, I would just enter the ID values as 1, 2, etc. and not as No. 1, No. 2.

Make your data table active and select Data>Filter and Query. Add all the columns to the query under the tab "Select Data".

Next you want to add some computed columns. As was pointed out earlier, it is better to have the column names as regular SAS variable names so they will not have periods or spaces. Therefore, to get what you want, we will create two computed columns, one to use to create the column name and one to create the label for the column name.
(You may have to tell SAS Enterprise Guide that you want to use labels as the column names. I cannot remember if that is the default or not. To do that, go to Tools>Options >Data General and check use labels for column names.)

I created two computed columns. These are the names and the formulas used to build the expression.
IDdatelabel cat('No. ',id,' ', put(date,date7.))
IDdate cat('No',id,'_', put(date, date7.) )

The results looks like:
ID Date IDdatelabel Time US IDdate
1 01JAN2006 No. 1 01JAN06 9 200 No1_01JAN06
1 01JAN2006 No. 1 01JAN06 10 300 No1_01JAN06
1 01JAN2006 No. 1 01JAN06 11 450 No1_01JAN06
1 02JAN2006 No. 1 02JAN06 9 150 No1_02JAN06
1 02JAN2006 No. 1 02JAN06 10 160 No1_02JAN06
1 02JAN2006 No. 1 02JAN06 11 200 No1_02JAN06
1 03JAN2006 No. 1 03JAN06 9 144 No1_03JAN06
1 03JAN2006 No. 1 03JAN06 10 122 No1_03JAN06
1 03JAN2006 No. 1 03JAN06 11 500 No1_03JAN06
2 01JAN2006 No. 2 01JAN06 9 235 No2_01JAN06
2 01JAN2006 No. 2 01JAN06 10 214 No2_01JAN06
2 01JAN2006 No. 2 01JAN06 11 145 No2_01JAN06
2 02JAN2006 No. 2 02JAN06 9 254 No2_02JAN06
2 02JAN2006 No. 2 02JAN06 10 789 No2_02JAN06
2 02JAN2006 No. 2 02JAN06 11 100 No2_02JAN06
2 03JAN2006 No. 2 03JAN06 9 500 No2_03JAN06
2 03JAN2006 No. 2 03JAN06 10 650 No2_03JAN06
2 03JAN2006 No. 2 03JAN06 11 450 No2_03JAN06

Next select Data>Transpose to transpose the data.
Select Task Roles.
Move US to Transpose variables.
Move IDdate to New column names.
Move Time to Group analysis by
Move IDdatelabel to New column labels.
Chose Options and uncheck Use Prefix.
Run the task.

Hopefully, I have included all of the steps and words and that this will help you. It is clear from this long description that often a picture is worth a thousand words.