SAS Support Communities

Here are the ODS graphics options I use most often. The height and width options may address your issue. I find that imagefmt=PDF results in better looking plots, but the resulting file size can be much larger than imagefmt=PNG, depending on the number of data points.   ODS GRAPHICS ON/reset border = off antialiasmax=1000000 height = 100pct width = 100pct imagefmt=PDF; ... View more

Thanks for your thoughtful reply. In my situation I have little to no influence on data originators. I can’t ask too much of them because they generally have limited resources, e.g., software and training for tools such as MS Access. I get the data for free, and I too have limited resources. I want to avoid the requirement for a special version of their data files just for me. There’s risk in asking data originators to perform tasks such as copy/paste, and the possibility of file versioning errors when saving a copy in CSV. So far I haven’t encountered a “fatal flaw” in this approach, as far as I know. I encourage data originator to follow best practices, such as using consistent and meaningful column names, providing metadata such as units, formatting numeric fields, and using the ISO format for dates. I agree that this approach results in “a bit of a manual process”, as it’s a bother to create a control file for each source of data, but I think it results in higher data integrity where the data originator uses Excel. This program can be easily modified to work with CSV files, and this is on my list of future enhancements. ... View more

My solution: 1. Import Excel worksheet with all columns as character by using the MIXED and GETNAMES options. For example: PROC IMPORT DATAFILE='...' OUT=rawdata_character DBMS=EXCEL REPLACE; SHEET="..." MIXED=YES; GETNAMES=NO; RUN; 2.  Assuming that the row of column headers is within the guessingrows range this technique will force all columns to be character. The SAS variable names will be F1, F2, F3, ...      Note that the formatted value in each cell will be imported. e.g., value=5.1324234, formatted cell=5.13, SAS value=5.13. 3. In data steps find the row containing the column headers (hard code it if it's always the same row), read the headers into SAS macro variables, and loop through the macro variables to create a SAS numeric variable for each column.  When reading the row of headers remove special characters in order to produce a valid SAS name. When converting character to numeric (e.g., numeric_name = INPUT(character_name,BEST.) check if the conversion was successful (if character_name NE '' and numeric_name = . then conversion failed) and output unsuccessful conversions to a data file for further investigation. You can enhance the data steps to handle issues such as inconsistent column header names by using a crosswalk mapping of each name to a standard name. Bill. ... View more

Hi:   Just wondering if you noticed the posting https://communities.sas.com/thread/38666. It shows how you can build on Robert's code in the reply above to include information other than a link in a mouse over. Bill. ... View more

Hi Arthur:   Sure, thanks for the offer. ... View more

Hi: I had problems getting the Graphics Template Language to work properly, so I created a new variable for the one-to-one line coordinates and added plotted it by adding a "series" statement to the SGPLOT and SGPANEL Procs. It works perfectly. The program is shown below. For now I've given up trying to get one-to-one lines on the PROC SGSCATTER / Matrix output. ------------------------------ ODS LISTING CLOSE; GOPTIONS RESET=ALL ftext='Helvetica' ; OPTIONS orientation=landscape papersize=letter nonumber nodate; ODS graphics on/reset border = off height = 7.5in width = 9in ; ODS PDF file='c:\temp\pm_intercomparison_hourly_data.pdf' DPI=300 style=analysis; TITLE1 'CAPMoN PM Intercomparison Study--Hourly' j=r h=.8 "&SYSDATE9"; TITLE2 ; * Calculate the min/max of all observations (ignoring the BY variable) for each of the two plotting variables; PROC MEANS DATA=alldata NOPRINT; VAR egbbam1 egbbam2; OUTPUT OUT=min_max(KEEP=min_var1 min_var2 max_var1 max_var2) min=min_var1 min_var2 max=max_var1 max_var2; RUN; * Create new variables to store the MIN and MAX values of the two variables. Insert the MIN pairing on the first observation of the BY group, and the MAX pairing in the last observation of the BY group.; DATA alldata_with_1_to_1; SET alldata; BY yr_month; RETAIN one 1 _found 0; IF FIRST.yr_month THEN DO; SET min_max POINT=one; one_to_one_x = MIN(min_var1,min_var2); one_to_one_y = one_to_one_x; END; IF LAST.yr_month THEN DO; SET min_max POINT=one; one_to_one_x = MAX(max_var1,max_var2); one_to_one_y = one_to_one_x; END; * Store the value of the first non-missing BY group variable in the macro variable first_by_variable_value; IF yr_month NE '' AND NOT _found THEN DO; CALL SYMPUTX('first_by_variable_value',yr_month); _found = 1; END; LABEL one_to_one_y = '1-1 Line'; DROP min_var1 min_var2 max_var1 max_var2 _found; RUN; * For SGPLOT output where all BY groups are on one plot, include the one-to-one pairings only for the first BY group.; DATA alldata_for_sgplot; SET alldata_with_minmax; IF yr_month NE "&first_by_variable_value" THEN DO; one_to_one_x = .; one_to_one_y = .; END; RUN; PROC SGPLOT DATA=alldata_for_sgplot; series x=one_to_one_x y=one_to_one_y / lineattrs=(pattern=dot thickness=2px color=gray) ; REG x=egbbam1 Y=egbbam2 / CLM GROUP=yr_month; RUN; QUIT; PROC SGPANEL DATA=alldata_with_1_to_1; PANELBY yr_month / columns=4 rows=4 uniscale=all ; reg x=egbbam1 Y=egbbam2 ; series x=one_to_one_x y=one_to_one_y / lineattrs=(pattern=dot thickness=2px color=gray) ; RUN; QUIT; ODS PDF CLOSE; ODS GRAPHICS OFF; ... View more

Just to clarify, by a one-to-one line in a scatter plot I mean a straight line going from the min(x1,x2) to max(y1,y2). For example, min(x1,x2)=2, max(y1,y2)=10, one-to-one line = (2,2) to (10,10). Ideally, the horizontal and vertical axis will have identical ranges and the plot area will be square, resulting in a line at 45 degrees. I guess I could determine the min and max in a PROC MEANS, and merge these values with the data set, but since the axis range is determined by the SG procedure, the line drawn by this method is not guaranteed to span the entire graph. I’m wondering if this can be accomlished using the Graph Template Language. ... View more