Date: Sat, 28 Feb 2009 23:32:42 -0600
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: Excel Tagsets question
In-Reply-To: <b7a7fa630902272014m27158b49x7658a085e9bbf972@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
So I've read a dozen or so SUGI papers and played around with PROC REPORT,
and have a much better understanding of what's going on. Except that now I
have more questions than I came in with, I think... lol.
* I can't get the font_face inside the cell to work. IE, the line changing
'Carol' to courier. I also tried something more appropriate for my report -
adding an up arrow, ie ascii 0233 in Wingdings, to Carol's name, and that
didn't go any better.
The compute block:
compute name;
select (name);
when ('Alfred')
name = 'Alfredo';
when ('Alice')
name = 'Alice1|{super 1}1';
when ('Carol')
name = 'Carol|{style=[font_face=Wingdings]}é';
otherwise
;
end;
endcomp;
Alice works fine [that's directly from Jack's code], but Carol does not. I
modified it some obviously (the original code, from several posts back,
didn't work for me either). I am definitely using the newest Tagset, 9.1.3
SAS.
* Can I embed styles in the variable value itself - this is particularly in
the case of the above; when I do my stat testing, and want to apply the
arrow, I was assuming I'd have to insert the é literal into the string and
then change the font somehow (either in VB or in SAS) after the fact. Can I
just put the style code in the string value?
* SAS (9.1.3) complains about the row + 1; in the code. I'm not sure why.
It seems to perform its function - ie, the colors change - but it gives me a
bunch of:
ERROR 180-322: Statement is not valid or it is used out of proper order.
one for every row in the data, when I leave it in. I tried defining it with
a noprint, but to no avail - still error, and now I have 'WARNING: row is
not in the report definition' as well...
define row / noprint;
compute weight;
row + 1;
if row in (1, 2) then
call define(_row_, 'style', 'style=[background=cxAA0000]');
else if mod(row, 2) = 0 then
call define(_row_, 'style', 'style=[background=cxAA5500]');
else
call define(_row_, 'style', 'style=[color=cxFFFFFF
background=cxDADADA]');
endcomp;
I'm working on a test example (per _NULL_'s suggestion), though I am not
familiar enough with proc plan to do it that way, so I'm just going to do it
manually (yay, call ranuni!). Once I start to feel my way around this more
I'll post it for critiques/criticisms/general mirth :)
Thanks,
Joe
On Fri, Feb 27, 2009 at 10:14 PM, Joe Matise <snoopy369@gmail.com> wrote:
> Wow, that's perfect... after reading a couple of SUGI papers that even
> makes sense! I'm going to be one of those "Why didn't I start using PROC
> REPORT sooner" people I suspect... :)
>
> Thanks again,
> Joe
>
>
> On Fri, Feb 27, 2009 at 10:05 PM, Jack Hamilton <jfh@alumni.stanford.org>wrote:
>
>> Here's some code that uses inline formatting to put a superscript in the
>> middle of one name and change the font of another one:
>>
>> =====
>> options nocenter nodate nonumber; title;
>> dm 'clear log; clear out;' log;
>> ods escapechar='|';
>> data class / view=class;
>> length Name $60;
>> set sashelp.class;
>> run;
>> ods tagsets.excelxp file='c:\temp\procreport.xlm' style=sansprinter;
>> proc report data=class nocenter missing nowindows;
>>
>> column ('-All Variables-'
>> ('=Demographics=' Name Age Sex)
>> ('_Measures_' Height Weight)
>> );
>> define sex / width=3;
>> compute name;
>> select (name);
>> when ('Alfred')
>> name = 'Alfredo';
>> when ('Alice')
>> name = 'Alice1|{super 1}1';
>> when ('Carol')
>> name = '|{style [font_face=courier]Carol}';
>> otherwise
>> ;
>> end;
>> endcomp;
>> compute weight;
>> row + 1;
>> if row in (1, 2) then
>> call define(_row_, 'style', 'style={color=red}');
>> else if mod(row, 2) = 0 then
>> call define(_row_, 'style', 'style={color=orange}');
>> else
>> call define(_row_, 'style', 'style={color=white
>> background=gray90}');
>> endcomp;
>> run;
>> ods tagsets.excelxp close;
>> =====
>>
>> Inline formatting seems to work in ExcelXP 1.86 SAS 9.2, but I recall that
>> at one time it didn't work. I don't know if the version of the tagset or
>> the version of SAS that made a difference.
>>
>> Note that I had to expand the length of Names so the formatting commands
>> would fit. There are other ways to do this, but using a view was the
>> easiest.
>>
>> If you look at the Excel workbook, you'll see that "Carol" is black when
>> you expect it to be orange. That's because, by default, a CALL DEFINE
>> overrides any other call defines. There's a way in 9.2 to make them
>> additive, but the SAS document site seems broken tonight so I can't look up
>> how to do it.
>>
>>
>>
>>
>>
>> --
>> Jack Hamilton
>> jfh@alumni.stanford.org
>> Videtis illam spirare libertatis auram
>>
>>
>>
>>
>>
>> On Feb 27, 2009, at 6:23 pm, Joe Matise wrote:
>>
>> Had a feeling Proc Report would be the answer ... will read up on it so I
>>> understand all of what you've posted, though it does seem fairly transparent
>>> to me already.
>>>
>>> I intend to use ExcelXP tagsets (and yes, when I say 'sheets' I mean
>>> ultimately three worksheets in a workbook in Excel). The if/call
>>> define/etc. looks exactly what I was hoping for! Very nice. I will have to
>>> experiment some to see how it interacts with ExcelXP differently from HTML,
>>> but as they're not dissimilar I imagine it will not be difficult.
>>>
>>> A couple of perhaps simple questions;
>>> * Can you alter font size/face/style inside a cell(s) in this manner?
>>> Particularly, part of the data only (say, a superscript for a footnote
>>> number)?
>>> * Can you operate in a proc report block based on the value of the data,
>>> or is it only on row/etc.? Is this practically a datastep? I'm guessing
>>> this is where I go read SUGI posts... which I'll do this weekend :)
>>>
>>> Thank you very much for the advice!
>>>
>>> -Joe
>>>
>>> On Fri, Feb 27, 2009 at 7:36 PM, Jack Hamilton <jfh@alumni.stanford.org>
>>> wrote:
>>> The following code prints the first two data rows in red, then alternates
>>> white and orange for the remaining rows (when sent to HTML destination):
>>>
>>> =====
>>> proc report data=sashelp.class nocenter missing nowindows;
>>>
>>> column ('-All Variables-'
>>> ('=Demographics=' Name Age Sex)
>>> ('_Measures_' Height Weight)
>>> );
>>> define sex / width=3;
>>> compute weight;
>>> row + 1;
>>> if row in (1, 2) then
>>> call define(_row_, 'style', 'style={color=red}');
>>> else if mod(row, 2) = 0 then
>>> call define(_row_, 'style', 'style={color=orange}');
>>> else
>>> call define(_row_, 'style', 'style={color=white}');
>>> endcomp;
>>> run;
>>> =====
>>>
>>> This sets the text color; if you wanted to set the background color, you
>>> would use background= instead of color=.
>>>
>>>
>>>
>>> --
>>> Jack Hamilton
>>> jfh@alumni.stanford.org
>>> Videtis illam spirare libertatis auram
>>>
>>>
>>>
>>>
>>>
>>> On Feb 27, 2009, at 5:24 pm, Jack Hamilton wrote:
>>>
>>> On Feb 27, 2009, at 4:30 pm, Joe Matise wrote:
>>>
>>> More than likely the first of several questions as I venture into
>>> unknown
>>> territory with tagsets...
>>>
>>> I'm writing a multisheet report in Tagsets, each sheet a bit
>>> different/more
>>> complicated. I'm starting with the first one, and have a few
>>> hopefully
>>> simple questions for you tagset gurus out there ...
>>>
>>> Tagsets don't inherently have sheets. Do you mean that you are
>>> writing your own new tagset for some eventual destination (probably
>>> Excel, because that's what most people mean when they say "sheets" in
>>> a data context), or are you going to use the ExcelXP tagset, the
>>> MSOffice2K tagset, or something else? It sounds from your questions
>>> like you will be using one of the first two options, but it's not
>>> certain.
>>>
>>> Report 1 has a header row (row A) that is merged across all columns
>>> that
>>> have data (about 25), merged together. Then a header row (row B)
>>> that has a
>>> few classification groupings (think "Gender", "Race") that are
>>> merged across
>>> several columns (Gender across 2, Race across 5, etc.). Then it has
>>> a third
>>> row (C) that is the actual variable labels (no merged columns).
>>> Then it has
>>> the variables.
>>>
>>> If you are using the ExcelXP tagset with PROC REPORT, you would code
>>> something like this:
>>>
>>> ===== code
>>> proc report data=sashelp.class missing nowindows;
>>> column ('-All Variables-'
>>> ('=Demographics=' Name Age Sex)
>>> ('_Measures_' Height Weight)
>>> );
>>> define sex / width=3;
>>> run;
>>> ===== listing output
>>> ----------------All Variables-----------------
>>> ======Demographics====== ______Measures______
>>> Name Age Sex Height Weight
>>> Alfred 14 M 69 112.5
>>> Alice 13 F 56.5 84
>>> Barbara 13 F 65.3 98
>>> Carol 14 F 62.8 102.5
>>> Henry 14 M 63.5 102.5
>>> James 12 M 57.3 83
>>> Jane 12 F 59.8 84.5
>>> Janet 15 F 62.5 112.5
>>> Jeffrey 13 M 62.5 84
>>> John 12 M 59 99.5
>>> Joyce 11 F 51.3 50.5
>>> Judy 14 F 64.3 90
>>> Louise 12 F 56.3 77
>>> Mary 15 F 66.5 112
>>> Philip 16 M 72 150
>>> Robert 12 M 64.8 128
>>> Ronald 15 M 67 133
>>> Thomas 11 M 57.5 85
>>> William 15 M 66.5 112
>>> =====
>>>
>>> I used "-", "+", and "_" in the column headers to make the
>>> columnification more obvious in the listing destination. In Excel,
>>> these would appear as merged cells.
>>>
>>>
>>> What's the best approach to putting this out? Prior to this all of my
>>> reports have either been straight data (data step put _all_ ) or
>>> data+variable labels (proc print data=(data); run;) with just the
>>> one header
>>> row. Can I combine the two methods here (two data step put out
>>> rows, and
>>> then a straight proc print for the third row)? Or is data step put
>>> better/mandatory?
>>>
>>> You can do what I think you want with a single PROC REPORT, and that's
>>> what I would do unless there's some reason not to.
>>>
>>>
>>> Proc Print seems like it's more transparent to other
>>> users
>>>
>>> Unfortunately, that's true. I wish SAS put *much* more emphasis and
>>> PROC REPORT and PROC TABULATE in their training, because those procs
>>> have the potential to eliminate a lot of messy multi-step, multi-merge
>>> programs. In many cases a slightly complicated PROC REPORT will still
>>> be easier to understand than its sort+split+merge+datastep alternative.
>>>
>>>
>>> [I want people with minimal understanding of tagsets and SAS to be
>>> able to read and even potentially maintain some elements of this code]
>>>
>>> Depending on what you want to do, I think PROC REPORT can often do
>>> that. I think that someone with some knowledge of SAS and the ability
>>> to think could figure out why the code above produces the output it
>>> does, and even make simple changes.
>>>
>>> Being able to read it is of course not the same as being able to write
>>> it. And I wouldn't make that claim about TABULATE, which requires
>>> more study.
>>>
>>> but
>>> data step put obviously has more direct control.
>>>
>>> It depends on what you're trying to do. In some cases, the desired
>>> function can be requested in a simpler and more understandable manner
>>> using PROC REPORT or PROC TABULATE. In particular, if you are writing
>>> to a non-listing destination and want detailed control over the
>>> appearance of headers and cells, you get more control with PROC REPORT
>>> than with the data step. (For me, anyway - as I recall, you're not
>>> afraid of style templates, which I find almost completely opaque.)
>>>
>>> And then there's proc
>>> report, which I've never used but theoretically could be practical
>>> here
>>> [although there are no calculations].
>>>
>>> PROC REPORT may be exactly what you want. You don't have to do
>>> calculations, you can use it just for its better control of output.
>>> For example, if you want a blank column with a blank header in your
>>> output, it's much easier to do with PROC REPORT than with PROC PRINT.
>>>
>>> Also, two of the data rows (the first two) will have a slightly
>>> different
>>> background color and border than the others. Does that mean I
>>> pretty much
>>> have to use data step put in order to control the background for
>>> those two
>>> rows?
>>>
>>> No, you could do that with PROC REPORT. You'd need to use a compute
>>> block. You might be able to do it with a style template, but I would
>>> find that more difficult.
>>>
>>> Finally, the remaining data rows have alternating shading
>>> (orange/white/orange/white) for all rows. Again - does that direct me
>>> towards data step puts,
>>>
>>> How were you planning to set colors in data step puts?
>>>
>>> or can I do that via the proc print [or proc
>>> report]? I have little to no experience with altering row-level
>>> output,
>>> only columnar in the past...
>>>
>>>
>>> You could do that with PROC REPORT as well.
>>>
>>> I would start with some of Ray Pass's papers from SUGI, and Art
>>> Carpenter's book.
>>>
>>>
>>> --
>>> Jack Hamilton
>>> jfh@alumni.stanford.org
>>> Videtis illam spirare libertatis auram
>>>
>>>
>>>
>>
>
| 
