BookmarkSubscribeRSS Feed
alan0101
Obsidian | Level 7

Hi, I was hoping someone could help with the following.  I have a number of SAS datasets that I want to export onto the same sheet in an Excel spreadsheet.  This is the code that I am using :

 

proc export data=lib.FreqGender outfile="&ProjPath\V005.xlsx"
dbms=xlsx
replace;
sheet=Demographics;
run;

 

However, conceptually what I am trying to do is this :

 

proc export data=lib.FreqGender outfile="&ProjPath\V005.xlsx"
dbms=xlsx
replace;
sheet=Demographics;

range=gender;
run;

 

i.e. I have defined a cell range called gender in the sheet called demographics, where I want to export the contents of the SAS dataset called FreqGender.  Any ideas ?

3 REPLIES 3
Steelers_In_DC
Barite | Level 11

I have seen something online about range names but never when related to export, only import so I'm not able to help there.  If you give the actual range it should work though.  Range like this 'range =Sheet1!C1:E8'

Reeza
Super User

Are you talking about a named range?

 

If so if you use a libname to access the sheet you can write to the sheet, first drop it and then set it. However, I don't believe this works with the XLSX engine. If you're using the XLSX engine you'll need to specify the range as a range of cells, like @Steelers_In_DC has specified.

 

libname test pcfiles path= 'path to excel file';

 

proc sql;

drop table test.gender;

quit;

 

data test.gender;

set lib.freqgender;

run;

 

libname test;

PGStats
Opal | Level 21

I would use

 

libname xl Excel "&ProjPath\V005.xlsx";

proc sql;
drop table xl.gender;
create table xl.gender as
select * from lib.FreqGender;
quit;

libname xl clear;

Your named range will include column names on the first line. This works because dropping a table in Excel deletes the content but not the named range itself.

PG

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
  • 3 replies
  • 3975 views
  • 0 likes
  • 4 in conversation