topic Re: Issue in Proc export sheet name in SAS Programming

Issue in Proc export sheet name

Aayushi_17 — Thu, 21 Feb 2019 05:46:37 GMT

Hi ,

In proc export for excel I have given sheet name as

sheet='Value Level Terminology';

But am getting the output in excel sheet as

'Value_Level_Terminology';

I dont want this '_ ' between the sheet names.Does anyone know how to get the output as 'variable mapping'. thanks in advance.

Re: Issue in Proc export sheet name

SASKiwi — Thu, 21 Feb 2019 05:58:41 GMT

Try adding this OPTIONS statement before your export code:

options validvarname = any validmemname = extend;

Re: Issue in Proc export sheet name

andreas_lds — Thu, 21 Feb 2019 06:01:07 GMT

Please post the code used to export the file. Which SAS version are you using?

Re: Issue in Proc export sheet name

Aayushi_17 — Thu, 21 Feb 2019 06:04:35 GMT

am using 9.4 version.

proc export data=d_outi.variable
outfile='C\newfolder\pricesheet.xls'
label dbms=xls replace;
sheet='variable label';
run;

this is the code i used to export the excel file

Re: Issue in Proc export sheet name

andreas_lds — Thu, 21 Feb 2019 06:09:10 GMT

@Aayushi_17 wrote:

am using 9.4 version.

proc export data=d_outi.variable
outfile='C\newfolder\pricesheet.xls'
label dbms=xls replace;
sheet='variable label';
run;

this is the code i used to export the excel file

The options mentioned by @SASKiwi don't solve the issue seems to be a feature when creating files in the outdated excel-format xls. Can you change to xlsx?

proc export data=sashelp.class
      outfile="PATH\class.xlsx"
      label dbms=xlsx replace;
   sheet='variable label';
run;

Re: Issue in Proc export sheet name

Aayushi_17 — Thu, 21 Feb 2019 06:09:39 GMT

I tired this but still getting the same output

Re: Issue in Proc export sheet name

Aayushi_17 — Thu, 21 Feb 2019 06:14:12 GMT

Thanks this one worked

Re: Issue in Proc export sheet name

Satish_Parida — Thu, 21 Feb 2019 06:59:53 GMT

The only difference between the codes are dbms=xls(old) and dbms=xlsx. How this fixed this issue?
Asking this to get a clear clarification.

Re: Issue in Proc export sheet name

andreas_lds — Thu, 21 Feb 2019 07:04:30 GMT

@Satish_Parida wrote:
The only difference between the codes are dbms=xls(old) and dbms=xlsx. How this fixed this issue?
Asking this to get a clear clarification.

Could be a bug/feature in proc export. Afaik xls supports blanks in sheet-names. You will need to contact tech-support for more information.