The SAS Output Delivery System and reporting techniques

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

Reply
Regular Contributor
Posts: 153

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

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;

Frequent Contributor
Posts: 93

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

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.

Grand Advisor
Posts: 16,413

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

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

Respected Advisor
Posts: 4,649

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

A guess that I hope is worth testing:

 

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

Regular Contributor
Posts: 153

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

That will show name 'a' in the excel ,how to remove that? thanks!
Grand Advisor
Posts: 9,748

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

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=' ', *

Regular Contributor
Posts: 153

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

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
Grand Advisor
Posts: 9,748

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


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)?

 

 

Grand Advisor
Posts: 16,413

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

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

Grand Advisor
Posts: 9,748

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


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".

Grand Advisor
Posts: 16,413

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

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

Super User
Super User
Posts: 5,990

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

[ Edited ]

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;

 

SAS Employee
Posts: 263

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

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

Post a Question
Discussion Stats
  • 12 replies
  • 255 views
  • 5 likes
  • 7 in conversation