BookmarkSubscribeRSS Feed
ursula
Pyrite | Level 9

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!

5 REPLIES 5
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ursula
Pyrite | Level 9

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?

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ballardw
Super User

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.

ursula
Pyrite | Level 9

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 Smiley Happy).

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1115 views
  • 3 likes
  • 3 in conversation