Excel data unfortunately is very susceptible to being moved around, cell characteristics changing, etc.   My solution is to always import the entire worksheet, and then using base code edit both the cells that I expect to contain data, to ensure the contents are reasonable, and to ensure there's nothing I don't expect in the other cells. Otherwise, you're likely to keep processing worksheets and producing results, even when the cells no longer line up the way they should.   Tom ... View more

Just when you think you've seen them all! I love it!!   Tom ... View more

Let me start by saying that this is the first time I've really had to dive deep into the SAS graphics capabilities, and I'm absolutely amazed by how much I've been able to accomplish in such a short time! Hats off to all of the developers!   In terms of what I'm trying to do, my client has the bar chart shown below, and wishes to reproduce it exactly (two versions shown, good and bad):   The values for the label and for the legend labels are client supplied, we may be doing several hundred of these charts immediately, and we'll want the capability to produce them moving forward. The top one is perfect, but in the second one the label and one of the legend labels is too long, and overflows. I don't want to adjust the chart dimensions either vertically or horizontally to accommodate this issue. A simplified version of the code that I use to create the chart is here. data Have; input Var1 Var2; cards; 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 3 1 3 2 3 3 3 4 run; data AnnoText1; %sganno; /* Print the heading */ %sgtext(label="Label for the chart", anchor="center", x1space="graphpercent", y1space="graphpercent", justify="center", textfont="Arial", textweight="bold", textsize=13, x1=50, y1=93, textcolor="black", width=100); %sgtext(label="", reset="all"); /* Print the column headings above the three bars */ %sgtext(label="V1 Val 1", anchor="center", x1space="datavalue", y1space="graphpercent", justify="center", textfont="Arial", textweight="normal", textsize=10, x1=1, y1=82, textcolor="black", width=33); %sgtext(label="V1 Val 2", anchor="center", x1space="datavalue", y1space="graphpercent", justify="center", textfont="Arial", textweight="normal", textsize=10, x1=2, y1=82, textcolor="black", width=33); %sgtext(label="V1 Val 3", anchor="center", x1space="datavalue", y1space="graphpercent", justify="center", textfont="Arial", textweight="normal", textsize=10, x1=3, y1=82, textcolor="black", width=33); %sgtext(label="", reset="all"); /* Draw the three gray lines at the top of the bars */ do _i = 8 to 68 by 30; _x2 = _i + 24; %sgline(x1=_i, x2=_x2, y1=78, y2=78, drawspace="graphpercent", linecolor="CXD8D8D8"); end; %sgline(x1=0, x2=0, y1=0, y2=0, reset="all"); /* Draw the circles on the legend */ %sgoval(x1=18, y1=26, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CXDD5D4E"); %sgoval(x1=18, y1=26, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(x1=18, y1=20, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CXF7D659"); %sgoval(x1=18, y1=20, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(x1=18, y1=14, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CX53B0D1"); %sgoval(x1=18, y1=14, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(x1=18, y1=8, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CX82BA59"); %sgoval(x1=18, y1=8, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(reset="all"); /* Print the text on the legend */ %sgtext(label="Var 2 Value 1", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=26, textcolor="black", width=79); %sgtext(label="Var 2 Value 2", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=20, textcolor="black", width=79); %sgtext(label="Var 2 Value 3", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=14, textcolor="black", width=79); %sgtext(label="Var 2 Value 4", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=8, textcolor="black", width=79); %sgtext(label="", reset="all"); run; data AnnoText2; %sganno; /* Print the heading */ %sgtext(label="Label for the chart, but really long so that it fills up more than the space available for it on the chart and causes us a problem", anchor="center", x1space="graphpercent", y1space="graphpercent", justify="center", textfont="Arial", textweight="bold", textsize=13, x1=50, y1=93, textcolor="black", width=100); %sgtext(label="", reset="all"); /* Print the column headings above the three bars */ %sgtext(label="V1 Val 1", anchor="center", x1space="datavalue", y1space="graphpercent", justify="center", textfont="Arial", textweight="normal", textsize=10, x1=1, y1=82, textcolor="black", width=33); %sgtext(label="V1 Val 2", anchor="center", x1space="datavalue", y1space="graphpercent", justify="center", textfont="Arial", textweight="normal", textsize=10, x1=2, y1=82, textcolor="black", width=33); %sgtext(label="V1 Val 3", anchor="center", x1space="datavalue", y1space="graphpercent", justify="center", textfont="Arial", textweight="normal", textsize=10, x1=3, y1=82, textcolor="black", width=33); %sgtext(label="", reset="all"); /* Draw the three gray lines at the top of the bars */ do _i = 8 to 68 by 30; _x2 = _i + 24; %sgline(x1=_i, x2=_x2, y1=78, y2=78, drawspace="graphpercent", linecolor="CXD8D8D8"); end; %sgline(x1=0, x2=0, y1=0, y2=0, reset="all"); /* Draw the circles on the legend */ %sgoval(x1=18, y1=26, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CXDD5D4E"); %sgoval(x1=18, y1=26, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(x1=18, y1=20, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CXF7D659"); %sgoval(x1=18, y1=20, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(x1=18, y1=14, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CX53B0D1"); %sgoval(x1=18, y1=14, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(x1=18, y1=8, height=4, width=4.8484848485, drawspace="graphpercent", display="fill", fillcolor="CX82BA59"); %sgoval(x1=18, y1=8, height=2.8, width=3.3939393939, drawspace="graphpercent", display="fill", fillcolor="white"); %sgoval(reset="all"); /* Print the text on the legend */ %sgtext(label="Var 2 Value 1", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=26, textcolor="black", width=79); %sgtext(label="Var 2 Value 2, really long so that it folds over on top of the Value 3 label and doesn't look good on the chart", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=20, textcolor="black", width=79); %sgtext(label="Var 2 Value 3", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=14, textcolor="black", width=79); %sgtext(label="Var 2 Value 4", anchor="left", x1space="graphpercent", y1space="graphpercent", justify="left", textfont="Albany AMT", textweight="bold", textsize=11, x1=21, y1=8, textcolor="black", width=79); %sgtext(label="", reset="all"); run; options nodate nonumber; ods graphics / reset=all; ods graphics / height=4in width=3.3in outputfmt=png border=off; ods html path='C:\SAS Tech Support Query'; proc sgplot data=Have noautolegend noborder pad=(top=23pct bottom=33pct) sganno=AnnoText1; styleattrs datacolors=(CXDD5D4E CXF7D659 CX53B0D1 CX82BA59); vbar Var1 / group=Var2 groupdisplay=stack displaybaseline=off nooutline barwidth=.8; xaxis display=none; yaxis display=none; run; proc sgplot data=Have noautolegend noborder pad=(top=23pct bottom=33pct) sganno=AnnoText2; styleattrs datacolors=(CXDD5D4E CXF7D659 CX53B0D1 CX82BA59); vbar Var1 / group=Var2 groupdisplay=stack displaybaseline=off nooutline barwidth=.8; xaxis display=none; yaxis display=none; run; ods _all_ close; My desire was to evaluate these text strings, and determine if any of them would cause a problem prior to producing the chart. Corrective action could then be taken in advance. The options that you mention are absolutely perfect, in that they let me evaluate the length of the text strings taking into account the final formatting. I spent a delightful afternoon noodling around with them, and now my head hurts!   One small glitch; if the text field has a comma in it, the csv data isn't being rendered correctly; the text value isn't in quotes.   Thanks again for the quick response to this question!    Tom ... View more

Hi, Rita   SAS has a feature called "picture" formats. Believe it or not, the day of week default is 1 for Monday. This code snippet should help you.   Tom   proc format; picture dwf low-high = '%u' (datatype=date); run; data Have; InDay = '9sep2019:00:00:00'dt; format InDay datetime.; day_nbr = datepart(InDay); format day_nbr dwf.; output; run; ... View more

No, I wasn't meaning to denigrate your processing! 🙂   I simply meant that it sounds to me like your environment is taking a long time to process that volume of data. That's why I asked about the file size; if you had replied that it was gigabytes, I wouldn't have thought anything about it. Just to compare, I generated roughly the same data volume (1.6M records, 80 fields, 876MB). It only took five minutes to create the data, and less than 10 seconds to read it. This is on my PC. I'm wondering if you have a communications bottleneck somewhere that's slowing things down; it might be contributing to your "disconnect" issue as well (or maybe the "disconnect" issue is contributing to the slow processing). Anyhow, if you do decide to investigate, there are lots of experts on this board who would be happy to help you out!   Tom ... View more

"An import of a txt file containing 1.6m records by 80 columns" doesn't sound particularly "large" to me. I'm wondering if you have some other issues in your environment. Of course, fix your immediate problem, but once that's done, how large (in megabytes) is the text file? I'm curious about why it's taking so long.   Tom ... View more

Stand back! This is a job for SUPER PROCTRANSPOSE!   Make your life easier, convert your diagnosis values into a column, and then you can apply any tests you wish.   Tom   proc sort data=Have; by ID; run; proc transpose data=Have out=HaveTransposed; var Diag1-Diag5; by ID; run; ... View more