BookmarkSubscribeRSS Feed
GeorgeSAS
Lapis Lazuli | Level 10

Hello ALl,

 

I want to use ods tagsets.excelxp  generate excel file,I want the output in excel file start at column B instead of A (Name in clumn B,Sex in column C..so and so forth)

 

please help.

 

Thanks!

 

 

ods listing close;

ods tagsets.excelxp file="C:\temp\test.xls";

proc sql;

select * from sashelp.class;

quit;

ods tagsets.excelxp close;

ods listing;

run;

12 REPLIES 12
thomp7050
Pyrite | Level 9

Hello George,

 

I do not know tagsets.excelxp, but just in case you decided to go a different route, here is an option for you:

 

data _null_;
call symputx('nowyear', year(date()));
call symputx('nowmonth', month(date()));
call symputx('nowday', day(date()));
call symputx('nowhour', hour(datetime()));
call symputx('nowminute', minute(datetime()));
run;

%let myvar = %str(&nowyear)_%str(&nowmonth)_%str(&nowday)_%str(&nowhour)_%str(&nowminute); 
%let title = "F:\Main\Documents\EN_%str(&myvar).xlsx";

PROC EXPORT DATA=RPT_T1 outfile= &title dbms=xlsx replace;
sheet="RPT_T1";
run;

Where RPT_T1 is a dataset that selects only the columns that you specify.

 

Please ignore if using excelxp is a requirement.

Reeza
Super User

I don't believe there's an easy way to accomplish this 😞

Astounding
PROC Star

A guess that I hope is worth testing:

 

select ' ' as a, * from sashelp.class;

GeorgeSAS
Lapis Lazuli | Level 10
That will show name 'a' in the excel ,how to remove that? thanks!
ballardw
Super User

You won't see anything but there would be a null character if you use a LABEL=' ' after the "as a" code. Note that you can get null character (at least in Windows) by holding down the alt key and typing 255 on the numeric pad. The character doesn't actually type until the Alt key is released.

 

select ' ' as a Label=' ', *

GeorgeSAS
Lapis Lazuli | Level 10
Actually I don't want create the empty column, because I use style=, it will print the cells grid which I don't want it be in then the output.

Thanks
ballardw
Super User

@GeorgeSAS wrote:
Actually I don't want create the empty column, because I use style=, it will print the cells grid which I don't want it be in then the output.

Thanks

Which is adding an additional requirement not mentioned in the original post.

 

May I ask what is so important about a column that apparently isn't to hold any data and not have any cell borders appear (if I understand what your "cells grid" comment means)?

 

 

Reeza
Super User

@ballardw someone is programming to a requirement specification and isn't willing to use logic and practicality, or push back.  

 

Other options to get an empty space is to widen the margins to give the illusion of extra space. 

ballardw
Super User

@Reeza wrote:

@ballardw someone is programming to a requirement specification and isn't willing to use logic and practicality, or push back.  

 

Other options to get an empty space is to widen the margins to give the illusion of extra space. 


@Reeza I was sort of hoping that the OP had asked, and could therefore answer, because I find that sometimes when I ask about odd requirements it turns out the response is often "that's what we've always done" and a little discussion sometimes (not even often but sometimes) results in a change to process. Possibly an answer may have allowed a more targeted response such as something like margins, cell padding, borders or revealed all of the requirements instead of the "here's a suggestion" followed by "but we're not allowed to do that".

Reeza
Super User

@ballardw fair enough, hopefully @GeorgeSAS still responds to your question 🙂

Tom
Super User Tom
Super User

Should be easy using PROC SQL.

proc sql;
select ' ',* from sashelp.class;
quit;

I little more work using PROC PRINT since you need to make a new column first and to get an empty header you have to use the LABEL instead of the NAME for the column headers.  If the other variables have labels that are different than their names this will change how their columns are headed.

If you set the label to a tab then it doesn't appear in Excel.

data view1 / view=view1 ;
  retain _ ' ';
  set sashelp.class ;
run;
proc print data=view1 noobs label; 
  label _='09'x;
run;

Or you could set the label to the split character and use the SPLIT= option instead.

proc print data=view1 noobs split='#'; 
  label _='#';
run;

 

Vince_SAS
Rhodochrosite | Level 12

I think our Technical Support Department has a modified version of the ExcelXP tagset that has an option to control the starting cell.  You can open a track and inquire at:

 

http://support.sas.com/ctx/supportform/createForm 

 

If you're using the third maintenance for SAS 9.4 or later, then you can use the START_AT option of the Excel destination:

 

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...

 

Vince DelGobbo

SAS R&D

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
  • 12 replies
  • 1607 views
  • 5 likes
  • 7 in conversation