How to get Name start at coumn B instead of A in EXCEL using ods tagsets.excelxp

[GeorgeSAS]

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;

[thomp7050]

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]

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

[Astounding]

A guess that I hope is worth testing:

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

[GeorgeSAS]

That will show name 'a' in the excel ,how to remove that? thanks!

[ballardw]

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]

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]

---

@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]

@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]

---

@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]

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

[Tom]

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]

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