topic Re: How to retain the Variable Format when PROC EXPORT TO EXCEL in SAS Procedures

How to retain the Variable Format when PROC EXPORT TO EXCEL

Jack2012 — Tue, 09 May 2017 09:29:22 GMT

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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

RW9 — Thu, 13 Sep 2018 17:58:34 GMT

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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

art297 — Tue, 09 May 2017 19:08:56 GMT

@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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Jack2012 — Thu, 11 May 2017 01:35:26 GMT

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.

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Jack2012 — Thu, 11 May 2017 01:45:29 GMT

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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

art297 — Thu, 11 May 2017 01:49:31 GMT

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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Vince_SAS — Thu, 11 May 2017 02:33:48 GMT

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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

mich1 — Mon, 05 Mar 2018 18:45:48 GMT

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;

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

PavelD — Wed, 04 Jul 2018 12:18:12 GMT

@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?

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

art297 — Wed, 04 Jul 2018 12:53:18 GMT

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

Art, CEO, AnalystFinder.com

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

dht115 — Tue, 11 Jan 2022 21:54:12 GMT

I download the code from https://github.com/art297/Papers/blob/master/Excelling_to_Another_Level_with_SAS/src/exportxl.sas

Getting an error:

ERROR: Undetermined I/O failure.

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Nikhiljain22740 — Wed, 28 Jun 2023 07:46:21 GMT

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

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

RW9 — Wed, 28 Jun 2023 08:07:32 GMT

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.

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

dbf — Tue, 19 Nov 2024 01:46:10 GMT

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