BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Jack2012
Obsidian | Level 7

Dear All, 

 

I am facing an frustrating problem: when I use PROC EXPORT to export data (with many variables have format ) to EXCEL, I found the format is lost, e.g. SEX only appear 1 or 2  in EXCEL.

 

I have searched in our community, but no easy way. Like ODS EXCEL+PROC REPORT, since many variable have their formats, then it is not a fun thing to define each variable.

 

May I know if there is an easy way? 

 

Best,
Jack

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

It is not possible with proc export to retain formats.  It is a plain data dump to Excel - and then you get all the "features" of Excel like dropping preceding zeros and such like.  If you are creating a report and it needs to have special formatting and such like then you need to write the code to do this.  Up to now I would use ods tagsets.excelxp and proc report, however the ods excel now creates native XLSX files, so best to go with that if you can.  This should also retain the formatted value, for example:

 

proc format;
  value $sex
    "M"="Male"
    "F"="Female";
run;

data want;
  set sashelp.class;
  format sex $sex.;
run;

/* Note for older versions of sas you could use
ods tagsets.excelxp file="c:\test.xml";
   To get the same result */

ods excel file="c:\test.xlsx";
proc report data=want;
  columns _all_;
run;
ods excel close;

 

If you want special formatting from Excel then add a row for each column that needs it and use define / style=...

View solution in original post

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

It is not possible with proc export to retain formats.  It is a plain data dump to Excel - and then you get all the "features" of Excel like dropping preceding zeros and such like.  If you are creating a report and it needs to have special formatting and such like then you need to write the code to do this.  Up to now I would use ods tagsets.excelxp and proc report, however the ods excel now creates native XLSX files, so best to go with that if you can.  This should also retain the formatted value, for example:

 

proc format;
  value $sex
    "M"="Male"
    "F"="Female";
run;

data want;
  set sashelp.class;
  format sex $sex.;
run;

/* Note for older versions of sas you could use
ods tagsets.excelxp file="c:\test.xml";
   To get the same result */

ods excel file="c:\test.xlsx";
proc report data=want;
  columns _all_;
run;
ods excel close;

 

If you want special formatting from Excel then add a row for each column that needs it and use define / style=...

Jack2012
Obsidian | Level 7

Highly appreciated for your proposal. I think the ODS EXCEL works for me at present. 

Nikhiljain22740
Fluorite | Level 6

Thanks would like to know if we import back the same file in SAS will it be same as dataset we exported into excel. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi, do bear in mind this topic was closed 6 years ago.  Its a good idea to open new questions as things will have changed in that time.

In terms of will the data be the same when imported back, the answer is it will retain the data part, however anything SAS specific will not, so if you export:

A   B  C

1   2   3

 

You will get a dataset with columns named A,B,C, and one row of data 1,2,3, but you will not necessarily get the right length, format, informat.  And also depending on language settings/special characters etc. the data might be different too.

dbf
Calcite | Level 5 dbf
Calcite | Level 5

This doesn't work for a large dataset, you receive an out of memory error.

art297
Opal | Level 21

@Jack2012: There is an easy way. Download and run the free macro at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Then, the following example will do exactly what I think you said you want to do:

proc format;
  value $gender
  'M'='Male'
  other='Female'
  ;
  
  value age
  low-13='young'
  other='not as young'
  ;
run;

data class;
  set sashelp.class;
  format sex $gender.;
  format age age.;
run;

%exportxl(data=class,
                outfile=c:\art\class.xlsx,
                useformats=Y)

Art, CEO, AnalystFinder.com

 

Jack2012
Obsidian | Level 7

Thanks Art, the problem, after I read the code in the link, is the format of my data is not stored in the DICTIONARY library, actually there is no such library. I am lost which one to use as instead of DICTIONARY since I have no idea about the such library. 

PavelD
Obsidian | Level 7

@art297, I get these errors while running the macro:

 

MACROGEN(EXPORTXL): put m;

MACROGEN(EXPORTXL): rc=filename('code2inc');

MACROGEN(EXPORTXL): stop;

MACROGEN(EXPORTXL): end;

MACROGEN(EXPORTXL): run;

ERROR: Undetermined I/O failure.

 

MACROGEN(EXPORTXL): call system('cscript "P:\work\_TD8972_JAMES_\Prc2\PasteIt.vbs"' );

MACROGEN(EXPORTXL): run;

ERROR: Shell escape is not valid in this SAS session.

 

Does my admin need to allow me to run the VBS via shell or something?

art297
Opal | Level 21

Yes! The macro was designed to run on base SAS using a Windows operating system.

 

Art, CEO, AnalystFinder.com

art297
Opal | Level 21

The metadata for ALL SAS datasets is stored in the various dictionary files .. NO EXCEPTIONS! The files are automatically built and maintained by the software.

 

Art, CEO, AnalystFinder.com

 

Vince_SAS
Rhodochrosite | Level 12

You can run the code below to see some of the table metadata:

 

proc format;
  value $gender 'M'='Male'
                other='Female';
  
  value age low-13='young'
            other='not as young';
run; quit;

data work.class;
set sashelp.class;
format sex $gender.;
format age age.;
run;

title 'SASHELP.VCOLUMN - PROC PRINT';

proc print data=sashelp.vcolumn noobs label;
  where (libname eq 'WORK' and memname eq 'CLASS');
  var name type length format;
run; quit;

title 'DICTIONARY.COLUMNS - PROC SQL';

proc sql;
  select name, type, length, format
  from dictionary.columns
  where (libname eq 'WORK' and memname eq 'CLASS');
quit;  

 

Vince DelGobbo

SAS R&D

mich1
Obsidian | Level 7

Robot Very Happy

I had the same issue. I was able to get around it by loading the variable with the custom format to another string variable...

data YOURDATA;
     set YOURDATA;
     format SWAP $50.;
     SWAP = PUT(FORMATTED_VALUE, $CUSTOM_FORMAT.);
     run;

Bread Crumbs and Circuses for All

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 13 replies
  • 71619 views
  • 11 likes
  • 9 in conversation