How do I maintain variable formating when using TRANSPOSE in SAS EG 4.3?

[PQK]

I have a dataset with multiple columns and the row value is date. Whenever I use TRANSPOSE (Tasks > Data > Transpose) the formating for each column disappears. Simple example is as follows.

Dataset Ex:
Date-----Percent---Time
date1----91.7%-----0:13:00
date2----93.5%-----0:34:06

Desired Result:
-------------date1-----date2
Percent --91.7%----93.5%
Time------0:13:00---0:34:06

Actual Result:
-------------date1----date2
Percent---0.9166--0.9347
Time------790.2----2046
(the dashes inserted above are for clarity only)

I thought there must be a box or something I left unchecked in the TRANSPOSE screens, but I've tested several different data sets and clicked on every box and my transposed datasets still loose all their formatting.

I did some research online too and tried to insert the FORMAT function into the code (ex: FORMAT Percent PERCENT8.1) immediately after VAR and before the end of the task, but this didn't work.

I would be grateful if anyone has any suggestions about how to get TRANSPOSE to maintain my data formats!!

[Peter_C]

It worked for me with sashelp.class. The transpose task allowed me to pick the columns to transpose. I was able to assign a percent. format to height. I also picked sex and age as columns to transpose. Name was picked as grouping variable.
Results looked as I think you would wish.
Make sure your time and percent formats are assigned.

peterC

[ChrisHemedinger]

As Peter points out, you can right-click on the variable in the selector control on the Data page, select Properties, and then set various attributes such as Format and Label. Those should stick when you run the task, generating a FORMAT statement or LABEL statement as needed.

Chris

[PQK]

Thank you Peter.C and Chris@SAS for your replies.

The formats are correctly assigned within the transpose task. However, once I execute the transpose command the resulting transposed datatable drops all previous formatting. That is, if I right-click on the variable within the transpose task window and open the properties box, each variable is assigned to the correct format. It is only after I execute the transpose command that all formatting changes to a basic numeric default.

Incidentally, when I open the code editor window there is no FORMAT statement listed after the VAR or LABEL statement. Even if I manually enter the FORMAT statement for my variables, the resultant transposed datatable still looses all previous formatting. Also, the log shows no errors with my edition of the FORMAT statement.

I can try changing the code again; however I would hope that a function as simple as formatting would be accessible in EG without having to resort to code writing...

Maybe this is a bug in SAS EG 4.3?

[Peter_C]

sorry PQK,
I did not appreciate enough of the detail of your problem with transpose in SAS Enterprise Guide 4.3

Your experience is not a defect. And you might be able to achieve what you want, quite simply.

I can replicate the behaviour you see, by removing all string variables from my selection of columns to be transposed.

You can have all your formats transposed to one column if you add another column to the list to be transposed - this new column must not be numeric, but character or string-type.
If you wish time values and percentage values presented in the same column then these must become string-type character variables. That is what the transpose task does when the columns to be transposed are a mixture of numeric and character data-types. .

If a string-type output column does not provide what you need, then you might have to find an alternative to SAS Enterprise Guide.
In microsoft excel the data type presented in a column can vary from row to row. SAS expects the data rows of a column to share the same format - either percentage or time in this example, but not both.

However, to have varying formats down a column in SAS is possible, but like excel,you will have to define (or default) the format for every row in every column and store this in addition to storing the data. Then you will need to build a viewer which uses these defined/default formats That can be created with SAS/AF. It would present the data outside of SAS Enterprise Guide. and it needs base SAS installed on your desktop to run the viewer.

Would the transposed column becoming character be OK?

peterC

[PQK]

PeterC,

Many thanks for the additional explanation.

Your first suggestion worked beautifully. Resolving this format problem by leaving in one string variable is a very interesting solution. In fact all my variables for this dataset (30+) are a mixture of numeric formats. I would have never thought that leaving in just one string variable would have solved the problem.

Best Regards,

PQK