01-27-2013 07:05 PM
I have questions abou the sas dataset format:
1). I was using 'Proc format' step to change the values in sas dataset (Gender).
The original values are: 1 and 2 (numeric).
I want to change the value 1 as a 'Male' and 2 as a 'Female' by using the proc format step.
value change 1='male'
after done with the format, the values has been changed to either 'Male' or 'Female'. Then I tried to export the sas dataset into Excel file.
The problelm is, the "Gender" field in exported excel file did not show 'male' or 'Female', but it showed the original format (1 or 2) instead.
is it a way to mentain the formated value as 'Male' or 'Female' in the exported excel file?? Any advice will be really appreciate.
2). how to change the sas dataset's 'Type' from numeric into character?
Thank you very much in advance!
01-27-2013 08:04 PM
PROC FORMAT doesn't actually change the values in a dataset. Instead, it creates labels for values, which you can choose to display instead of displaying the value itself.
If you want to create a character value which would have the value of 'male' or 'female', you should use the PUT() function. Something like:
If you want to keep the name of the variable as gender, you can rename it when you read it in:
set have (rename=(gender=_gender));
Those character variables should export to Excel as text cells.
01-27-2013 08:34 PM
thanks Quentin for your prompt help!
my understanding is that the proc format only "display" the value we want, but not really change in the dataset, is that true?
01-27-2013 09:07 PM
Yes, that is true. When you associate a format with a variable,it changes how it is displayed, but does not change the value of the variable.
When you use the PUT() function as in my example, you are creating a new variable, and the value of that new variables will be the text string 'male' or 'female'.
01-28-2013 10:59 AM
You don't mention how you export to excel. One option would be to use ODS tagsets.Excelxp combined wiht proc print. Then in proc print you assign the formats you want. No data change needed plus you have some control over appearance.
01-28-2013 05:13 PM
The ODS tagsets option is working!! it's fantastic!!
The Excelxp actually is not an Excel file, it's a Xml file, but you could make it as Excel file.
1. open the xml file in excel.
2. save as excel file (in this step, you are not able to import this excel file into sas, it poped up an error message - I have no idea, why? but I did some research ).
3. copy the whole worksheet to another worksheet, and then delete the first worksheet, keep the copy one. this way the file is really as a real excel file, and you could import into SAS with no problem.