BookmarkSubscribeRSS Feed
joshua_james
Calcite | Level 5

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 🙂

8 REPLIES 8
Reeza
Super User

How are you exporting?

 

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

joshua_james
Calcite | Level 5

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;

Reeza
Super User

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

1)

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

 

2)

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

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

ods excel close;

Oligolas
Barite | Level 11

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;
________________________

- Cheers -

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Oligolas
Barite | Level 11

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.

________________________

- Cheers -

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 10082 views
  • 5 likes
  • 5 in conversation