topic Re: Proc format issue in SAS Procedures

Proc format issue

ursula — Mon, 28 Jan 2013 00:05:30 GMT

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.

for example:

proc format;

value change 1='male'

2='female'

run;

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!

Re: Proc format issue

Quentin — Mon, 28 Jan 2013 01:04:19 GMT

Hi,

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:

data want;

set have;

genderC=put(gender,change.);

run;

If you want to keep the name of the variable as gender, you can rename it when you read it in:

data want;

set have (rename=(gender=_gender));

gender=put(_gender,change.);

drop _gender;

run;

Those character variables should export to Excel as text cells.

HTH,

--Q.

Re: Proc format issue

ursula — Mon, 28 Jan 2013 01:34:42 GMT

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?

Re: Proc format issue

Quentin — Mon, 28 Jan 2013 02:07:38 GMT

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

--Q.

Re: Proc format issue

ballardw — Mon, 28 Jan 2013 15:59:48 GMT

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.

Re: Proc format issue

ursula — Mon, 28 Jan 2013 22:13:09 GMT

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.