topic Re: Removing variables names from output. in SAS Programming

Removing variables names from Excel output.

joshua_james — Mon, 07 Mar 2016 19:00:27 GMT

Hi guys

I am trying to export a dataset to a named range in excel. Everything is working fine EXCEPT it also outputs the variable names/headers, which I don't need.

Is there anyway to just export the data without the variable names/headers?

Thanks

Joshua 🙂

Re: Removing variables names from output.

Reeza — Mon, 07 Mar 2016 02:36:29 GMT

How are you exporting?

There's the putnames=no option, but I think that's valid only for text files.

Re: Removing variables names from output.

joshua_james — Mon, 07 Mar 2016 02:40:46 GMT

I have imported data from one excel workbook and I'm exporting it to a named range in another.

Here's what my code looks like for the export portion -

libname xls excel 'H:\TEST.xlsm';

proc datasets lib = xls nolist ;

delete salestest ;

quit ;

data xls.salestest ;

set THISMONTH ;

run ;

libname xls clear;

Re: Removing variables names from output.

Reeza — Mon, 07 Mar 2016 04:11:50 GMT

Unfortunately the only way I know how is DDE. Is that an option?

Also, since you obviously have a template of some kind, perhaps create a new range that contains the full export and then link first range back to named range - minus the headers.

Re: Removing variables names from output.

Ksharp — Mon, 07 Mar 2016 04:51:27 GMT

proc export data=sashelp.class outfile= 'c:\temp\xyz.xls' dbms=xls replace ;
putnames=no;
run;

ods excel file=.........;

proc report data=sashelp.class noheader nowd;
run;

ods excel close;

Re: Removing variables names from output.

RW9 — Mon, 07 Mar 2016 09:25:45 GMT

Hi,

One alternative, seeing as how you are using Excel specific functionality (hopefully not for anyt other purpose!), is to do the work in Excel itself. From SAS you can export a simple CSV file. In your main file (or another if you don't want macros in your main file), create a small VBA macro which opens the CSV, then copies the revleant data to your named region. The benefits of this are that you don't have to try to control Excel from SAS - yes you have DDE but it is decades old and no longer supported, and you have th full functionality of Excel to assign regions, copy various bits etc. Use the right tool for the job.

Re: Removing variables names from output.

Oligolas — Fri, 11 Mar 2016 15:34:45 GMT

Please consider exporting your data to excel as text, since excel may try to interprete your data and put a format on it, which would lead to data modification... I get shivers in my spine just thinking about it

try this:

ods tagsets.excelxp file="C:\workbook.xml" style=statistical
   options(frozen_headers='yes' sheet_interval='none' sheet_name="cars" autofilter='all'  autofit_height='yes');
 
   PROC REPORT data=sashelp.cars nowindows
      style(report)=[font=(Arial, 8pt) protectspecialchars=on]
      style(header)=[font=(Arial, 10pt, bold) just=center protectspecialchars=on]
      style(column)=[font=(Arial, 8pt) protectspecialchars=on];
      COLUMN MAKE MODEL TYPE ORIGIN DRIVETRAIN MSRP INVOICE ENGINESIZE CYLINDERS HORSEPOWER MPG_CITY MPG_HIGHWAY WEIGHT WHEELBASE LENGTH;
 
      DEFINE MAKE / display style=[just=l cellwidth=2.745cm tagattr='type:String format:Text'];
      DEFINE MODEL / display style=[just=l cellwidth=7cm tagattr='type:String format:Text'];
      DEFINE TYPE / display style=[just=l cellwidth=1.882cm tagattr='type:String format:Text'];
      DEFINE ORIGIN / display style=[just=l cellwidth=2.191cm tagattr='type:String format:Text'];
      DEFINE DRIVETRAIN / display style=[just=l cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE MSRP / display style=[just=l cellwidth=1.882cm tagattr='type:String format:Text'];
      DEFINE INVOICE / display style=[just=l cellwidth=2.473cm tagattr='type:String format:Text'];
      DEFINE ENGINESIZE / display style=[just=l cellwidth=4.227cm tagattr='type:String format:Text'];
      DEFINE CYLINDERS / display style=[just=l cellwidth=2.536cm tagattr='type:String format:Text'];
      DEFINE HORSEPOWER / display style=[just=l cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE MPG_CITY / display style=[just=l cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE MPG_HIGHWAY / display style=[just=l cellwidth=3.664cm tagattr='type:String format:Text'];
      DEFINE WEIGHT / display style=[just=l cellwidth=3.382cm tagattr='type:String format:Text'];
      DEFINE WHEELBASE / display style=[just=l cellwidth=3.945cm tagattr='type:String format:Text'];
      DEFINE LENGTH / display style=[just=l cellwidth=3.1cm tagattr='type:String format:Text'];
   RUN;
 
ods tagsets.ExcelXP CLOSE;

Re: Removing variables names from output.

RW9 — Fri, 11 Mar 2016 17:32:28 GMT

Unfortunately ods tagsets.excelxp neither creates Excel files, nor does it insert data into an existing Excel file. tagsets.excelxp as with any tagset, creates XML output, which can be interpreted by Excel and rendered as a spreadsheet, however is a native Excel file. And due to this also, you can't paste data to named ranges

Re: Removing variables names from output.

Oligolas — Mon, 14 Mar 2016 09:52:51 GMT

If you do not needed to write to a named range, you could run a vbs macro to convert the xml file to a native xlsx file (autmatically from SAS) and the job is done.