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

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

GeorgeSAS — Tue, 25 Apr 2017 19:20:01 GMT

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;

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

thomp7050 — Tue, 25 Apr 2017 19:36:04 GMT

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.

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

Reeza — Tue, 25 Apr 2017 19:38:37 GMT

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

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

Astounding — Tue, 25 Apr 2017 19:40:48 GMT

A guess that I hope is worth testing:

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

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

GeorgeSAS — Tue, 25 Apr 2017 20:30:57 GMT

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

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

ballardw — Tue, 25 Apr 2017 20:42:41 GMT

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

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

GeorgeSAS — Tue, 25 Apr 2017 21:03:46 GMT

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

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

Tom — Tue, 25 Apr 2017 21:32:54 GMT

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;

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

ballardw — Tue, 25 Apr 2017 22:24:43 GMT

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

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

Reeza — Wed, 26 Apr 2017 00:37:35 GMT

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

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

Vince_SAS — Wed, 26 Apr 2017 11:29:08 GMT

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#p09n5pw9ol0897n1qe04zeur27rv.htm

Vince DelGobbo

SAS R&D

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

ballardw — Wed, 26 Apr 2017 15:29:03 GMT

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

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

Reeza — Wed, 26 Apr 2017 17:44:29 GMT

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