Hi and Thanks for the vote of confidence, Reeza. However, when I take SAS out of the picture and just go to Excel to an empty sheet, if I put a space into a cell, then ISBLANK returns FALSE and if I test a cell into which nothing has been typed, then ISBLANK returns TRUE.
So the challenge with any MISSING= or MISSTEXT= options in TABULATE or any coding in REPORT or with using a FORMAT is that they all place -something- even if the -something- is a blank character (space). I do not know what the Excel representation of "empty" is. I played around with blank and '00'x (which in some databases, considered NULL) but got the same result (FALSE) as shown in the screen shot.
Since I typically do my graphs with ODS GRAPHICS or SAS/GRAPH, it wouldn't occur to me to use Excel for this. ODS Graphics will return gaps for missing values, which is the original reason given for the need to have an isblank=TRUE cell. The BREAK option in SGPLOT inserts a break in the line for missing values, which can be represented as blanks. Here's the code I tried. I did NOT use TAGSETS.EXCELXP since it does not support graphs, (by Microsoft design) -- so I just used ODS EXCEL with SGPLOT.
Here's the code I used:
data test;
input month sales;
datalines;
1 10
2 20
3 30
4 .
5 50
6 60
;
run;
option missing=' ';
%let myWorkLocation = %sysfunc(pathname(work));
ods excel file="&myworkLocation./testnew.xlsx"
options(sheet_interval="none");
proc tabulate data = test missing;
class month;
var sales;
table (month='')*(sales='' *sum='')/ misstext=' ';
run;
proc sgplot data=test;
series x=month y=sales / break markers datalabel;
run;
ods excel close;
cynthia
@ProcWes wrote:
Yes, that's right, they're not high enough quality to present in my business world. Putting in "a hell of a lot of work" to come out with amateur/clinical looking graphics is not something I am interested in.
I do find that belief interesting as my introduction to Excel graphs was that they were much poorer in quality than what I was generating in SAS before every seeing Excel.
And tedious.
I moved to a shop that used Excel for the graphics. I remember spending several days exporting data, highlighting, moving and then going into the resultant basic graphs to set additional properties. At my previous job i had written programs to provide graphs (at least as good as those for the reports in Excel) that provided diagnostics for literally hundreds of instruments in about 10 times as many charts as that report in Excel. Every single day. And scheduled so they were ready when we came into the office.
I submit the actual difference is familiarity and not quality. Your shop probably has years of accumulated templates or experience working with Excel so you think things get done "better" due to that familiarity.
By the way that massive daily graphics process was self-taught in under a year and involved creating a graphic interface to modify the code reading changing instrument installs, with part of the code providing diagnostics to tell when the actual time the instrument package changed in the telemetry. And that was before SAS features that allow creating graphs such as this (which I would have loved to have as I was working with temperature, precipitation, humidy and streamflow data). How long would it take to provide this in Excel? SAS provides example code on there website for this and many other very interesting graphs.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
4633 views
-
3 likes
-
-
