The SAS Output Delivery System and reporting techniques

ods excel with data_null_ and excelxp tagsets

Reply
Regular Contributor
Posts: 155

ods excel with data_null_ and excelxp tagsets

Hi Cynthia,

when i am using data_null_ i want to place the values at certain point but they are not working.I am just trying to test wether in excel
i can place the values where ever i like.

can yo please let me know the options Message was edited by: JasonNC
SAS Super FREQ
Posts: 8,742

Re: ods excel with data_null_ and excelxp tagsets

Hi:
I think there's a disconnect somewhere. I'm not exactly sure what you mean when you say you "want to place the values at certain point". But let me talk, in general about DATA _NULL_ and FILE PRINT with PUT statements and maybe then you'll understand why it's not working the way you want.

Originally, long ago, in a version of SAS far, far away, there was only the OUTPUT window -- what folks now call the LISTING window or the LISTING destination (or SYSOUT on the mainframe or a .LST file in batch mode). There was no ODS. That long ago, there was barely Excel. There may have been VisiCalc. At that point in time, SAS and VisiCalc didn't really know about each other. And, since Excel wasn't born yet, nobody wanted to get their output from SAS into Excel.

In those old days, when we had to code our programs on punch cards. most of the output came out all in landscape mode, in upper case letters (no upper/lowercase printers) in a fixed pitch font. Most of your choices were whether you got 10 characters per horizontal inch of paper or 12 characters per horizontal inch of paper in one line and you could get 6 lines of output to the vertical inch. Not a lot of flexibility.

Since you knew exactly how many characters per inch and how many lines per inch, it was possible to predict exactly where on an output page you would write, if you wrote a program with DATA _NULL_, FILE PRINT and PUT statements. You could treat your whole .LST or SYSOUT file like a big piece of graph paper. This meant that the print position of @15 would be in the same place on line 1, line 2, line 3, etc of the output. And it didn't matter whether you were writing a "skinny" letter like lowercase 'l' or a "fat" letter like a lowercase 'w' -- if you put the 'l' in position 15 on one line and the 'w' in position 15 on another line, then the 2 letters would line up, one above the other. That is the beauty of fixed pitch fonts and absolute positioning.

Alas, the world of typography and printers moved beyond fixed pitch fonts into the world of proportional fonts, where the letter 'l' takes up less horizontal space than the letter 'w'. (I know, we haven't even gotten to Excel yet.) For example, in a proportional font compare the 30 letter 'l' with the 30 letter 'w' on these 2 lines:
llllllllllllllllllllllllllllll
wwwwwwwwwwwwwwwwwwwwwwwwwwwwww

Next, compare the 2 lines in a "fixed pitch" font:
[pre]
llllllllllllllllllllllllllllll
wwwwwwwwwwwwwwwwwwwwwwwwwwwwww
[/pre]

As you can see, position 15 will not "line up" in the proportional spaced font while it will "line up" in the fixed pitch font.

Your program is a typical example of writing "free-format" DATA _NULL_ output to the LISTING destination or OUTPUT window. That form of output does not work in "regular" ODS destinations (like RTF, PDF and HTML) because those destinations are different from the LISTING destination. For one thing, they're not like a big piece of graph paper. The number of characters you can get "per horizontal inch" in RTF, PDF and HTML destinations will depend or vary, based on the font size used for your output. And, most RTF, PDF and HTML output created by ODS is tabular in nature -- which means it is NOT "free-format" in nature. Tabular output has the same number of columns on every row -- for example, you might have columns with the headers "Name" "Age" "Height" and "Weight" -- each row of the tabular output would have the same number of columns -- so there are 4 columns in the above example -- there is no "print position 15" or PUT @15 that is possible with a tabular report like this with 4 columns.

A NAME value on one row could be Fred. A NAME value on another row could be Sebastian. Both of these values would be written to column 1 on the report. In a proportional font table, the 'F' in Fred and the 'S' in Sebastian would both start at the left side of the first table column, but there's no guarantee, after that first letter, that the other letters in the names would line up. Consider another row with the name Ella...the 'E' would line up with the 'F' and the 'S', but the other letters wouldn't line up:
Fred
Sebastian
Ella

This is a totally different concept for report writing than using "free-format" DATA _NULL_ to writing LISTING destination output in a fixed pitch font.

The ODS developers are working on a way for you to use ODS REGION and ODS LAYOUT statements (currently pre-production) in order to generate "free-format" output, such as a brochure or an invoice -- but these statements will work best in "paged" destinations like PDF and possibly RTF. They will work less well, if at all, with non-paged destinations like ODS HTML or ODS TAGSETS.EXCELXP.

But, that's not what your example was about and besides, ODS LAYOUT isn't going to help you here.

So, let's take a look at Excel. As far as I know, Excel is laid out in rows and columns -- not print positions. My Excel 2010 has columns of A, B, C, D, E, F, etc and rows that start at 1 and increment 1 number for each row. So, the location A1 is in the top left-hand corner of a worksheet and B1 is right next to A1, and C1 is right next to B1, etc. A2 is under A1, etc. So in looking at your program, where did you envision your PUT statement would write these text strings???
[pre]
PUT @1 "xxxx-xxxx" @7 ":"
@9 "xxxxxxxxxxxxxxxx" +14 " xxxxx" +19 "xxxx:" +0 ":" +1 "xxxxx" /
@1 "xxxx:xxxxx" @7 ":" @9 "S" @57 "xxxxxxxx" +45 "xxxx:" +0 ":" +1 "xxxxxxx" /
@50 "xxxxxxxxxxxxx"/
@60 "xxxxxxxxxxxxxxxxxxxxx";
[/pre]

I can't imagine where @1 and @7 are in an Excel worksheet. If I use this program
[pre]
ods listing;

ods html file='c:\temp\testnull.html' style=sasweb;
ods rtf file='c:\temp\testnull.rtf';
ods pdf file='c:\temp\testnull.pdf';
ods csv file='c:\temp\testnull.csv';
ods tagsets.excelxp file='c:\temp\testnull_xp.xls' style=sasweb;

data _null_;
file print;
put @1 "0........1.........2.........3.........4.........5.........6.........7.........8";
put @1 "1---5----0----5----0----5----0----5----0----5----0----5----0----5----0----5----0";
PUT @1 "xxxx-xxxx" @7 ":"
@9 "xxxxxxxxxxxxxxxx" +14 " xxxxx" +19 "xxxx" +0 ":" +1 "xxxxx" /
@1 "xxxx:xxxxx" @7 ":" @9 "S" @57 "xxxxxxxx" +45 "xxxx:" +0 ":" +1 "xxxxxxx" /
@50 "xxxxxxxxxxxxx"/
@60 "xxxxxxxxxxxxxxxxxxxxx";
run;

ods _all_ close;
[/pre]


to write lines to the LISTING destination, this is what I get in the LISTING window and ONLY in the LISTING window:
[pre]
0........1.........2.........3.........4.........5.........6.........7.........8
1---5----0----5----0----5----0----5----0----5----0----5----0----5----0----5----0
xxxx-x:xxxxxxxxxxxxxxxxx xxxxx xxxx: xxxxx
xxxx:x:xSx xxxxxxxx
xxxx:: xxxxxxx
xxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxx

[/pre]

I would never expect RTF, PDF or HTML or TAGSETS.EXCELXP output to look anything like the LISTING output. In fact, when I open the CSV file or the XML output in Excel, I see everything I've written in column A. So I suspect that's what you mean when you say "it's not working".

In fact, if you look at my paper on DATA _NULL_ and Table templates, the introduction talks about these differences between how ODS works with FILE PRINT and how LISTING destination works with FILE PRINT:
http://www2.sas.com/proceedings/sugi30/088-30.pdf

Usually, when folks talk about placing some piece of text directly into a specific cell, they mean something like they want to put NAME into Excel location C2, and then they want to put AGE into Excel location D2, with HEIGHT in E2 and WEIGHT in E3 or something like that. That type of "cell placement" is only possible with named ranges using the Excel LIBNAME engine and/or using DDE or OLE-DB techniques. ODS does not allow exact placement of one piece of information in one particular cell. Since ODS is only making tabular output, the output from ODS starts in the upper left of the Excel sheet (A1) and if you have titles turned on, then the titles take up row 1 and then the output table is put underneath the titles. If you have titles turned off, then the tabular output starts in A1.

If you are trying to put your own Print Headers or Print Footers into the TAGSETS.EXCELXP output file, then there are suboptions to do that. But I am still having a hard time figuring out what you are trying to accomplish with your PUT statements in the context of a workbook/worksheet scenario.

Anyway -- I know it seems like this was a long explanation, but the trip down memory lane was for the purpose of explaining why DATA _NULL_ was never intended to produce "free-format" output in ODS destinations.

cynthia
Regular Contributor
Posts: 155

Re: ods excel with data_null_ and excelxp tagsets

Hi Cynthia,

The explanation was really fantastic.I appreciate your patience and support.

I got your point regarding the cells A1 that was right.

The problem i am facing is when i am using the code which i have posted is working fine for pdf .

when i ran the same code with excel path it was printing fine but i am getting dots in the row beginning.

So I used the option "ASCII_DOTS=No" to remove it.But what happening is the title in case(1) (layout below) was printing in the row beginning instead of middle.

When we use proc report we are getting the table layout with no problems.But for data_null_ we are facing problems.

In excel i should get like this


(1) The problem is coming at the report title.

I want it to print in the middle. But it was printing near the beginning of the row.

In case (2)

when i have some thing like Report in the beginning it is printing in the middle other wise it is printing in the beginning of the row.

when we use proc report it is automatically generating table layout.In proc report we are using Line @1 and there is no problem.
Is there any other way to generate table layout in excel using data_null_ Message was edited by: JasonNC
SAS Super FREQ
Posts: 8,742

Re: ods excel with data_null_ and excelxp tagsets

Hi:
I did not mean to hold out any hope that DATA _NULL_, FILE PRINT, PUT statements and TAGSETS.EXCELXP would ever produce the type of "free-format" output that you want. Yes, it looks sort of OK in RTF and PDF because the entire area for the report is defined as a "batch" area and generally, FILE PRINT with PUT statements will write into the batch area and while it's not exactly like LISTING, sometimes it's good enough.

I would NEVER expect DATA _NULL_ to work the way you want in TAGSETS.EXCELXP. If you want to continue to work on this, then I suggest you open a track with Tech Support so that the folks with the most experience in DATA _NULL_ and with the most experience with Microsoft Spreadsheet Markup Language XML can provide advice on this question.

cynthia
Valued Guide
Posts: 2,174

Re: ods excel with data_null_ and excelxp tagsets

hi HasonNC

with tagsets.excelXP, I have generated a "header page" for an excel workbook with proc print of just two columns (ref and description). For your scenario perhaps one column would be enough.

If you need only a few rows, use titles ( embed the titles to see in excel sheet) and use j=C before the title strings to have these centered. You will need a proc print or data-file-print step to deliver the titles.

but as Cynthis says, there is no excelxp way to specify position other than table/row/column.

good luck
peterC
Ask a Question
Discussion stats
  • 4 replies
  • 321 views
  • 0 likes
  • 3 in conversation