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,
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
BY date time;
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.
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.
input ID Date date9. Time kWh;
format date date9.;
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
DATA=WORK.TEMP(KEEP=kWh ID Date Time)
BY Date Time;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
BY date time;
proc print data=trans;
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.
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.) )
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.