SAS experts-  I'm asking for your help again... I need to use PROC EXPORT to send SAS datasets to Excel, but Excel will not accept a variable label that contains a dot.  Instead, Excel wants the label to have # to produce a dot (log file below).  http://support.sas.com/kb/16/842.html Currently, I am eliminating the dot on the way in from Oracle, but will have to solve this soon. My folks can deal with "E. Coli" not having a dot, but "Grain Size % in 2.76-3.91 um" requires it to make sense. I don't want the # in my SAS labels, and am using the split255 macro for output. http://support.sas.com/kb/36/904.html I have been messing with PROC DATASETS and trying to use the TRANSLATE function, but so far have been only able either to delete the labels, or make them "translate('label', '#', '.')"  (oops!) Thanks so much for your help! Wendy T 1    DATA TEST_DOT ; 2    INPUT NODOT ONEDOT TWODOT SEPDOT ; 3    LABEL NODOT='No Dot' ONEDOT='One.Dot' TWODOT='Two..Dots' SEPDOT='Sep.arated 3  !  . Dots' ; 4    DATALINES ; NOTE: The data set WORK.TEST_DOT has 1 observations and 4 variables. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       user cpu time       0.00 seconds       system cpu time     0.00 seconds       Memory                            247k       OS Memory                         5232k       Timestamp            4/30/2012  11:12:09 AM 6    ; 7    RUN ; 8 9    PROC EXPORT DATA=WORK.TEST_DOT OUTFILE="&data_req\tester.xlsx" 9  ! DBMS=EXCEL2007 REPLACE LABEL ; 10   RUN ; ERROR: Bind parameters: Invalid column name: One.Dot WARNING: File deletion failed for _IMEX_.TEST_DOT.DATA. ERROR: Export unsuccessful.  See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE EXPORT used (Total process time):       real time           1.24 seconds       user cpu time       0.03 seconds       system cpu time     0.18 seconds       Memory                            527k       OS Memory                         5744k       Timestamp            4/30/2012  11:12:10 AM 11 12 13   DATA TEST_POUND ; 14     INPUT NODOT ONEDOT TWODOT SEPDOT ; 15       LABEL NODOT='No Dot' ONEDOT='One#Dot' TWODOT='Two##Dots' 15 ! SEPDOT='Sep#arated # Dots' ; 16     DATALINES ; NOTE: The data set WORK.TEST_POUND has 1 observations and 4 variables. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       user cpu time       0.00 seconds       system cpu time     0.00 seconds       Memory                            189k       OS Memory                         5744k       Timestamp            4/30/2012  11:12:10 AM 18   ; 19   RUN ; 20 21   PROC EXPORT DATA=WORK.TEST_POUND OUTFILE="&data_req\tester.xlsx" 21 ! DBMS=EXCEL2007 REPLACE LABEL ; 22   RUN ; NOTE: "TEST_POUND" range/sheet was successfully created. NOTE: PROCEDURE EXPORT used (Total process time):       real time           0.99 seconds       user cpu time       0.00 seconds       system cpu time     0.04 seconds       Memory                            479k       OS Memory                         5744k       Timestamp            4/30/2012  11:12:11 AM ... View more

SAS folks- I've been playing around with this for quite awhile, and need some help. A colleague has lots of txt files in a directory, and is trying to read them with SAS.  The filename includes the date. Unfortunately, they have a little twist that I have so far been unable to unravel - the data we want starts on line 7, is multiple (unknown) records per line, and -9999 is null data and does not need to be read. I have no problem inputting one file in the infile statement.  This reads the multiple values per line, kicks out the null data, and I am able to pull out the date from the filename. DATA STRANGEDATA ;   LENGTH MYINFILE  $ 99 ;     INFILE 'X:\ES\EA\Wildlife\2000\abc20000601.txt' FILENAME=MYINFILE FIRSTOBS=7 LRECL=35000 PAD ;        INPUT DEPTH @@ ;        IF DEPTH > 0 ;          SAVEFILE=MYINFILE ;          Y=COMPRESS(SAVEFILE,,'DK') ;          SASDATE=MDY(SUBSTR(Y,9,2), SUBSTR(Y,11,2), SUBSTR(Y,5,4)) ;          FORMAT SASDATE DATE9. ;       OUTPUT ; RUN; However, when I change the filename to include a wildcard, I can't stop it reading the first 6 lines of subsequent files.  I've been fooling around with EOV= but can't seem to make it work. INFILE 'X:\ES\EA\Wildlife\2000\abc*.txt' FILENAME=MYINFILE FIRSTOBS=7 LRECL=35000 PAD ; So, I went at it from another method - making a file with the filenames, and reading the files in turn. This successfully picks up all the files in the list, but only the first entry on a line.  x 'del index.lst'; x 'dir /b X:\ES\EA\Wildlife\2000\abc*.txt > index.lst'; x 'exit' ; DATA STRANGEDATA ;   LENGTH MYINFILE $99 ;     INFILE 'index.lst' PAD MISSOVER;       INPUT MYINFILE $ 1-30;        FULLFILE='X:\ES\EA\Wildlife\2000\'||MYINFILE ;     INFILE FILE FILEVAR=FULLFILE END=DONE PAD MISSOVER FIRSTOBS=7 LRECL=35000;        DO UNTIL (DONE);          INPUT DEPTH ;           IF DEPTH > 0 ;            Y=COMPRESS(MYINFILE, , 'DK');            SASDATE=MDY(SUBSTR(Y,5,2), SUBSTR(Y,7,2), SUBSTR(Y,1,4)) ;            FORMAT SASDATE DATE9. ;          OUTPUT;        END; RUN ; Adding  @@ to the INPUT and deleting MISSOVER from INFILE to pick up the multiple occurrences per line gets nothing, and I know there should be observations from running the files separately. I have to be missing something obvious here - any help you can give me would be much appreciated.  Three of the many files are attached here. Thanks for any help you can give me!  We are running 9.2 on Windows. WendyT ... View more

Hi folks - I've been sending plain html reports as attachments to an E-mail for quite awhile, but now would like to send some analysis results. I was hoping to send a couple SGPLOT graphics and html tables inside an E-mail, with a clickable link to see the whole regression analysis. Unfortunately, I get the dreaded red X in the E-mail instead of the graphics. After doing some searching, I came up with usage note 43716 that says that SAS does not support sending graphics under  9.1 TS1M3 SP4.  http://support.sas.com/kb/43/716.html Has anyone sucessfully done this, or is it still unresolved? (we have 9.2 at the moment) Thanks for any help you can give me! Wendy T. ... View more

Good morning folks! I'm stumped and need your help. Basic premise - we are playing with various scenarios in our data, and one of them is to adjust datasets based on critical values. The logic flow is to take the original dataset (which is divided into segment groupings), generate the statistic of interest by segment, and compare it to the chosen critical values for that segment. If the calculated statistic is smaller than the critical value for a segment, send the data out to a 'completed' dataset (ITSDONE). If the calculated statistic is larger than the critical value for a segment, delete the largest value in the dataset, and repeat until the critical value test is satisfied (NOTDONE). Unfortunately, I can't seem to figure out how to stop the do loop when the critical value test is satisfied (when NOTDONE is empty). The particular combination we are looking at right now requires 751 iterations for all segments to meet the test criteria. ...until we change something (which we immediately will). I need to be able to do this without 'knowing' anything but the critical value and statistic. Thanks so much for your help! Wendy T. Dataset START already contains the critical value (K_CRIT), and is sorted by descending values of the variable ANGLE_K by SEGMENT. Dataset STACKER (to receive dataset ITSDONE) already exists, and contains one line with null values. DATA LOOP ; SET START ; PROC MEANS NOPRINT N DATA=LOOP ; BY SEGMENT ; VAR ANGLE_K ; OUTPUT OUT=ORIG1 (DROP= _TYPE_ _FREQ_) N=ORIGINAL_N ; RUN ; RUN ; %MACRO CUTTOPVALUE ; %DO I=1 %TO 751 ; PROC MEANS NOPRINT DATA=LOOP ; BY SEGMENT ; VAR ANGLE_K ; OUTPUT OUT=MEDIANS (DROP=_TYPE_) MEDIAN=MEDIAN ; RUN ; PROC SQL NOPRINT ; CREATE TABLE CHECKER AS SELECT LOOP.*, MEDIANS.MEDIAN, MEDIANS._FREQ_ FROM LOOP, MEDIANS WHERE LOOP.SEGMENT=MEDIANS.SEGMENT ; QUIT ; DATA ITSDONE NOTDONE (DROP=MEDIAN _FREQ_) ; SET CHECKER ; IF MEDIAN LE K_CRIT THEN OUTPUT ITSDONE ; ELSE OUTPUT NOTDONE ; RUN ; DATA STACKER ; SET ITSDONE STACKER ; IF SEGMENT = '' THEN DELETE ; PROC SORT ; BY SEGMENT ; RUN ; DATA LOOP ; SET NOTDONE ; IF _N_ = 1 THEN DELETE ; RUN ; %END ; %MEND CUTTOPVALUE ; %CUTTOPVALUE ; DATA FINISHED ; MERGE STACKER ORIG1 ; BY SEGMENT ; RUN ; ... View more

I need some help! I've been going around in the documentation for a couple of days and I'm stumped. I'm trying to set up a program that will E-mail folks when equipment needs to be maintained, and all of the information I need is contained in an xml file, along with other project information. I have been able to set up a macro that reads all the individual tables (15) out of the xml file and spits them out to an Excel workbook so I can look at them. LIBNAME EQUIP XML "\\inputpath\OurEquipmentData.xml" ; PROC SQL NOPRINT ; SELECT MEMNAME INTO: TABLENAME SEPARATED BY ' ' FROM SASHELP.VTABLE WHERE LIBNAME='EQUIP' ; QUIT; %MACRO READEQUIP ; %LET TABLECOUNT=%sysfunc(COUNTW(&TABLENAME)) ; %DO ZZ=1 %TO &TABLECOUNT ; %LET DSN=%SCAN(&TABLENAME,&ZZ) ; DATA &DSN ; SET EQUIP.&DSN ; RUN ; PROC EXPORT DATA=WORK.&DSN OUTFILE="\\outputpath\EXCEL_EQUIPMENT_FILE.xlsx" DBMS=EXCEL REPLACE LABEL ; SHEET="&DSN" ; RUN ; %END ; %MEND ; %READEQUIP ; However, the datetime format in the xml file is a bit strange, and comes in as text. The informat E8601DT. will do the translation to a SAS datetime. NEWDATE=INPUT(DATE,E8601DT.); FORMAT NEWDATE DATETIME23. ; (or TEMPEXPDATE, as the case may be) Here's where I'm stuck: 8 of the 15 tables have a datetime variable. In one table, it is named TEMPEXPDATE, and it is DATE in the rest. I can get a listing of the tables that contain "DATE" as part of the variable name from SASHELP.VCOLUMN. PROC SQL NOPRINT ; CREATE TABLE DATENAMES AS SELECT MEMNAME, NAME FROM SASHELP.VCOLUMN WHERE LIBNAME='EQUIP' AND NAME CONTAINS 'DATE' ; QUIT; But I'm not sure how to use that information. Your help would be very much appreciated! Wendy T. ... View more

SAS folks- I have been looking in the documentation for a SAS function for converting latitude/longitude from degrees minutes seconds to decimal degrees. I could use the formula VALUE = (degrees) + (minutes/60) + (seconds/3600); but was looking for a shortcut. Thanks for your help! Wendy T. ... View more