data _null_; x=6274.4999; put x hex16.; /* here is the problem non-terminating mantissa cannot represent the number exactly - you have lost already. 6274.4999 40B8827FF9724745 like 1/3 in decimal (never is exact) 0.3333333333333 */ x=62744999; put x hex16.; /* got it exactly 62744999 418DEB4D38000000 */ x=6274.5; put x hex16.; /* got it exactly (when mantissa is a power of 2**(-n)) 6274.5 40B8828000000000 */ /* possible solution; summing in the integer domain is exact (given no overflow); I don not think it has to be this complex; */ input numchr $9.; put numchr; numchr=compress(numchr,'.'); exact_integer=input(numchr,9.); put exact_integer hex16.; * in microcode this should be a bit shifting operation and should be exact?; res=round(exact_integer,10000); res=res/10000; put res 9.4; cards4; 6274.4999 6274.5000 6274.5001 ;;;; run;quit; ... View more

I believe it is possible to uninstall EG. Also I think you can install SAS without EG, EG is very large? ... View more

Select a file from a pop up dialog box and list the file requires Python inspired by ( this is a link to this message) https://goo.gl/eKhTYt https://communities.sas.com/t5/Base-SAS-Programming/filedialog-sas-9-3-vs-sas-9-4/m-p/345718 Doable in R but I like Python and SAS/WPS for these kinds of problems HAVE ( A directory with some files (chose your own directory) ============================================================== Directory of d:/txt fyl.txt hex.txt hexout.txt myfile.txt utf16le.txt UtlChkFyl.txt ==> click on this one WANT ( select and print file ) =============================== click on UtlChkFyl.txt And this will appear in log %macro utlchkfyl(file); %if %sysfunc(fileexist(&file)) ge 1 %then %do; %let rc=%sysfunc(filename(temp,&file)); %let rc=%sysfunc(fdelete(&temp)); %end; %else %put The file &file does not exist; %mend utlchkfyl; WORKING CODE (PYTHON) ===================== fyl = tkFileDialog.askopenfilename(filetypes=[('Text files', '*.txt')]); INVOCATION ========== %SelDir(c:/txt); * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %macro SelDir(dir); %utl_submit_py64(" import os; import tkFileDialog; import os; os.chdir('&dir'); fyl = tkFileDialog.askopenfilename(filetypes=[('Text files', '*.txt')]); fo= open ('d:/txt/fyl.txt', 'w'); fo.write(fyl+'\n'); fo.close(); "); * could also use filevar in infile; * but I think it is worth learning and using this method; data _null_; if _n_=0 then do; %let rc=%sysfunc(dosubl(' data _null_; infile "d:/txt/fyl.txt"; input; fylvar=strip(_infile_); call symputx("fyl",fylvar); run;quit; ')); end; infile "&fyl"; input; put _infile_; run;quit; %mend SelDir; %SelDir(d:/txt); %macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands"; * write the program to a temporary file; filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v; data _null_; length pgm $32755 cmd $1024; file py_pgm ; pgm=&pgm; semi=countc(pgm,';'); do idx=1 to semi; cmd=compbl(cats(scan(pgm,idx,';'))); if cmd=:'.' then cmd=substr(cmd,2); put cmd $char384.; putlog cmd $char384.; end; run; run;quit; %let _loc=%sysfunc(pathname(py_pgm)); %put &_loc; filename rut pipe "C:\Python_27_64bit/python.exe &_loc"; data _null_; file print; infile rut; input; put _infile_; run; filename rut clear; filename py_pgm clear; %mend utl_submit_py64; ... View more

Replace sheet 'class' in a macro enabled (xlsm) workbook This just links back to this message - I cross posied to SAS-L inspired by https://goo.gl/pACrZt https://communities.sas.com/t5/Base-SAS-Programming/Problem-in-export-the-data-to-xlsm-file/m-p/345438 I could not do this with R, so I swithched to Python. As a side note Puthon Win32com and RDCOM may eliminate the need to DDE? HAVE macro enabled excel 2010 64bit workbook d:/xls/classcop.xlsm ================================================================== MALES ONLY SASHELP.CLASS in the XLSM WORKBOOK +----------------------------------------------------------------+ | A | B | C | D | E | +----------------------------------------------------------------+ 1 | NAME | SEX | AGE | HEIGHT | WEIGHT | +------------+------------+------------+------------+------------+ 2 | ALFRED | M | 14 | 69 | 112.5 | +------------+------------+------------+------------+------------+ ... +------------+------------+------------+------------+------------+ N | WILLIAM | M | 15 | 66.5 | 112 | +------------+------------+------------+------------+------------+ [class] * manually add this VBA macro; /* Sub AddTicks() Dim LastPlace, Z As Variant, X As Variant LastPlace = ActiveCell.SpecialCells(xlLastCell).Address ActiveSheet.Range(Cells(1, 1), LastPlace).Select Z = Selection.Address 'Get the address For Each X In ActiveSheet.Range(Z) 'Do while If Len(X) > 0 Then 'Find cells with something X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X)) '39 is code for tick Else X.FormulaR1C1 = "" 'If empty do not put tick End If If X = "TIUQ" Then Exit Sub End If Next End Sub */ WANT replace sheet class with 'Females only SASHELP.CLASS" and keep VBA code ============================================================================ REPLACE WIT FEMALES ONLY +----------------------------------------------------------------+ | A | B | C | D | E | +----------------------------------------------------------------+ 1 | NAME | SEX | AGE | HEIGHT | WEIGHT | +------------+------------+------------+------------+------------+ 2 | ALICE | F | 14 | 69 | 112.5 | +------------+------------+------------+------------+------------+ ... +------------+------------+------------+------------+------------+ N | MARY | F | 15 | 66.5 | 112 | +------------+------------+------------+------------+------------+ [class] WORKING CODE (note keep_vba option) ==================================== wb = load_workbook(filename='d:/xls/classcop.xlsm', read_only=False, keep_vba=True); libname sd1 "d:/sd1"; call missing(of _all_); output xel.class; manually add a VBA macro and save as macro enabled d:/xls/class.xlsm FULL SOLUTION ============== * delete dataset; proc datasets lib=sd1; delete class; run;quit; * delete spreadsheets; * create dataset and xlsx file; %let fyl=d:/xls/class.xlsx; %utlfkil(&fyl); * delete if exist; * create empty workbook with males only SASHELP.CLASS; libname xel "&fyl"; options validvarname=upcase; libname sd1 "d:/sd1"; data xel.class; set sashelp.class(where=(sex='M')); run;quit; libname xel clear; * manually add a VBA macro and save as macro enabled d:/xls/class.xlsm; /* Sub AddTicks() Dim LastPlace, Z As Variant, X As Variant LastPlace = ActiveCell.SpecialCells(xlLastCell).Address ActiveSheet.Range(Cells(1, 1), LastPlace).Select Z = Selection.Address 'Get the address For Each X In ActiveSheet.Range(Z) 'Do while If Len(X) > 0 Then 'Find cells with something X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X)) '39 is code for tick Else X.FormulaR1C1 = "" 'If empty do not put tick End If If X = "TIUQ" Then Exit Sub End If Next End Sub */ * copy the macro enabled workbook and update the copy(backup VBA code); %bincop(in=d:/xls/class.xlsm,out=d:/xls/classcop.xlsm); /* You can do this by hand */ * create sashelp.class with females only; libname sd1 "d:/sd1"; data sd1.classf; set sashelp.class(where=(sex='F')); run;quit; * _ _ _ __ _ _| |_| |__ ___ _ __ | '_ \| | | | __| '_ \ / _ \| '_ \ | |_) | |_| | |_| | | | (_) | | | | | .__/ \__, |\__|_| |_|\___/|_| |_| |_| |___/ ; %utl_submit_py64old(" from openpyxl.utils.dataframe import dataframe_to_rows; from openpyxl import Workbook; from openpyxl import load_workbook; from sas7bdat import SAS7BDAT; with SAS7BDAT('d:/sd1/classf.sas7bdat') as m:; . clas = m.to_data_frame(); print(clas); wb = load_workbook(filename='d:/xls/classcop.xlsm', read_only=False, keep_vba=True); ws = wb.get_sheet_by_name('class'); rows = dataframe_to_rows(clas); for r_idx, row in enumerate(rows, 1):; . for c_idx, value in enumerate(row, 1):; . ws.cell(row=r_idx, column=c_idx, value=value); wb.save('d:/xls/classcop.xlsm'); "); * _ __ ___ __ _ ___ _ __ ___ ___ | '_ ` _ \ / _` |/ __| '__/ _ \/ __| | | | | | | (_| | (__| | | (_) \__ \ |_| |_| |_|\__,_|\___|_| \___/|___/ ; * macros; %macro utl_submit_py64old(pgm)/des="Semi colon separated set of py commands"; * write the program to a temporary file; filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v; data _null_; length pgm $32755 cmd $1024; file py_pgm ; pgm=&pgm; semi=countc(pgm,';'); do idx=1 to semi; cmd=cats(scan(pgm,idx,';')); if cmd=:'.' then cmd=substr(cmd,2); put cmd $char96.; putlog cmd $char96.; end; run; run;quit; %let _loc=%sysfunc(pathname(py_pgm)); %put &_loc; filename rut pipe "C:\Python_27_64bit/python.exe &_loc"; data _null_; file print; infile rut; input; put _infile_; run; filename rut clear; filename py_pgm clear; %mend utl_submit_py64; %macro BinCop( in=\\filesrv04\stat\peggcsf\sd01\meta\datamart\bonepain_2008\docs\rogerdeangelis\bne.xls ,out=c:\pis\bne.xls ) / des="Copy a binary file"; data _null_; infile "&in" recfm=n; file "&out" recfm=n; input byt $char1. @@; put byt $char1. @@; run; %mend BinCop; ... View more

If you happen to have 'full' local SAS with the 'old text editor' you can do the following. Suppose you have this code in your editor data class; set sashelp.class; run; Highlight either sashelp.class or class and type 'xlsh' on the clean command line. Excel will open with the the sasdatset imported. ==> xlsh You can also put the command macro on a function key. (highlight - hit key fyi macro xls for last dataset) SAS removed this functionality in all editors after the old text editor. The command macro has to be in your autocall library. I also have shift-rmb to list 40 obs from last dataset with obs number. and many more. Much faster that SAS viewer and you can cut and paste. and excel will open with the SAS dataset importted. %macro xlsh /cmd ; store;note;notesubmit '%xlsha;'; run; %mend xlsh; %macro xlsha/cmd; filename clp clipbrd ; data _null_; infile clp; input; put _infile_; call symputx('argx',_infile_); run; %let __tmp=%sysfunc(pathname(work))\myxls.xlsx; data _null_; fname="tempfile"; rc=filename(fname, "&__tmp"); put rc=; if rc = 0 and fexist(fname) then rc=fdelete(fname); rc=filename(fname); run; libname __xls excel "&__tmp"; data __xls.%scan(__&argx,1,%str(.)); set &argx.; run;quit; libname __xls clear; data _null_;z=sleep(1);run;quit; options noxwait noxsync; /* Open Excel */ x "'C:\Program Files\Microsoft Office\OFFICE14\excel.exe' &__tmp"; run;quit; %mend xlsha; ... View more

General Proc Transpose https://goo.gl/f6ZvY0 https://communities.sas.com/t5/General-SAS-Programming/General-Proc-Transpose/m-p/344744 I like 'proc corresp' the best for this kind of problem, it identifies John with 4 levels, C twice. HAVE Up to 40 obs WORK.HAVE total obs=11 Obs DR LETTERS DATE 1 John C 01JAN2017 2 John F 01JAN2017 3 John R 01JAN2017 4 Ben C 16FEB2017 5 Ben R 16FEB2017 6 Alex C 02MAR2017 7 Alex R 02MAR2017 8 Alex F 02MAR2017 9 John C 25DEC2016 10 John R 25DEC2016 11 John F 25DEC2016 WANT obs DR C F R 1 Alex yes yes yes 2 Ben yes no yes 3 John yes yes yes Simple solutions solutions \ PROC FREQ (just a display) ========= Table of DR by LETTERS DR LETTERS Frequency|C |F |R | Total ---------+--------+--------+--------+ Alex | 1 | 1 | 1 | 3 ---------+--------+--------+--------+ Ben | 1 | 0 | 1 | 2 ---------+--------+--------+--------+ John | 2 | 2 | 2 | 6 ---------+--------+--------+--------+ Total 4 3 4 11 PROC CORRESP Obs LABEL C F R SUM 1 Alex 1 1 1 3 2 Ben 1 0 1 2 3 John 2 2 2 6 4 Sum 4 3 4 11 * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; data have; informat Dr $4. Letters $1. Date date10.; input Dr $ Letters $ Date ; cards4; John C 1-Jan-17 John F 1-Jan-17 John R 1-Jan-17 Ben C 16-Feb-17 Ben R 16-Feb-17 Alex C 2-Mar-17 Alex R 2-Mar-17 Alex F 2-Mar-17 John C 25-Dec-16 John R 25-Dec-16 John F 25-Dec-16 ;;;; run;quit; *_ | |_ _ __ __ _ _ __ ___ _ __ ___ ___ ___ | __| '__/ _` | '_ \/ __| '_ \ / _ \/ __|/ _ \ | |_| | | (_| | | | \__ \ |_) | (_) \__ \ __/ \__|_| \__,_|_| |_|___/ .__/ \___/|___/\___| |_| ; * get rid of dups (ie 2 Cs); proc sort data=have out=havunq nodupkey; by dr letters; run;quit; * create variable with 'yes' drop date; data havchr(drop=date); set havunq; yesno='yes'; run;quit; /* Up to 40 obs WORK.HAVCHR total obs=8 Obs DR LETTERS YESNO 1 Alex C yes 2 Alex F yes 3 Alex R yes 4 Ben C yes 5 Ben R yes 6 John C yes 7 John F yes 8 John R yes */ proc transpose data=havchr out=havxpo; by dr ; id letters; var yesno; run;quit; /* Up to 40 obs WORK.HAVXPO total obs=3 Obs DR _NAME_ C F R 1 Alex YESNO yes yes yes 2 Ben YESNO yes yes 3 John YESNO yes yes yes */ data want; set havxpo; array chrs _character_; do over chrs; if chrs=' ' then chrs='no'; end; run;quit; /* Up to 40 obs WORK.WANT total obs=3 Obs DR _NAME_ C F R 1 Alex YESNO yes yes yes 2 Ben YESNO yes no yes 3 John YESNO yes yes yes */ proc print data=want; run;quit; * couple of possibilities; * ___ ___ _ __ _ __ ___ ___ _ __ / __/ _ \| '__| '__/ _ \/ __| '_ \ | (_| (_) | | | | | __/\__ \ |_) | \___\___/|_| |_| \___||___/ .__/ |_| ; Ods Exclude All; Ods Output Observed=want; Proc Corresp Data=have Observed dim=1; Table dr, letters; Run; Ods Select All; proc print data=want;run; If you want counts; Obs LABEL C F R SUM 1 Alex 1 1 1 3 2 Ben 1 0 1 2 3 John 2 2 2 6 4 Sum 4 3 4 11 * __ / _|_ __ ___ __ _ | |_| '__/ _ \/ _` | | _| | | __/ (_| | |_| |_| \___|\__, | |_| ; proc freq data=have; tables dr*letters/sparse norow nocolumn nopercent; run;quit; Table of DR by LETTERS DR LETTERS Frequency|C |F |R | Total ---------+--------+--------+--------+ Alex | 1 | 1 | 1 | 3 ---------+--------+--------+--------+ Ben | 1 | 0 | 1 | 2 ---------+--------+--------+--------+ John | 2 | 2 | 2 | 6 ---------+--------+--------+--------+ Total 4 3 4 11 /* Obs DR _NAME_ C F R 1 Alex DATE yes yes yes 2 Ben DATE yes no yes 3 John DATE yes yes yes */ ... View more

If you do not have named ranges book1, book2 use '[shee1$]' within the passthru ... View more

Taking care to get the maximum length of a excel string before importing Taking care to get the maximum length of a excel string before importing inspired by https://goo.gl/fiZXXR https://communities.sas.com/t5/Base-SAS-Programming/Append-multiple-dataset/m-p/344558 Issue I have this two datasets in excel format.After importing these two excel sheets I want to append book1 & book2. If I use append table in SAS EG then I got "transg" which takes the length of "female" from book2. You may sometimes get away without specifying character length, but it is better to passthru and check the lengths. I suspect odbc passthru may only support length upto 255 bytes. No sure of OLE-DB. You ned to passthru for this issue? HAVE ( two sheets in one workbook ) ==================================== d:/xls/books.xlsx sheet BOOK1 +------------------+ | A | +------------------+ 1 | BOOK | +------------------+ 2 | Gender | +------------------- 3 | female | +------------------- 4 | male | +------------------- [BOOK1] d:/xls/books.xlsx sheet BOOK2 +------------------+ | A | +------------------+ 1 | BOOK | +------------------+ 2 | Gender | +------------------- 3 | female | +------------------- 4 | transgender | +------------------- 4 | male | +------------------- [BOOK2] WANT (following SAS dataset ) ================================ p to 40 obs from want total obs=7 bs BOOK 1 Gender 2 female 3 male 4 Gender 5 female 6 transgender 7 male WORKING CODE connect to excel (Path="d:\xls\books.xlsx"); select max(len(book)) as lenmax * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; %utlfkil(d:/xls/books.xlsx); * delete if exists; libname xel "d:/xls/books.xlsx"; data xel.book1; informat book $11.; input Book ; cards4; Gender female male ;;;; run;quit; data xel.book2; informat book $11.; input Book ; cards4; Gender female transgender male ;;;; run;quit; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; * notes you need bckslashes for this to work d:\xls\books.xlsx if uncomfortable with DOSUBL just pull the code out and run outside the datastep ; %symdel maxlen; libname xel "d:/xls/books.xlsx"; data want; if _n_=0 then do; %let rc=%sysfunc(dosubl(%nrbquote( proc sql dquote=ansi; connect to excel (Path="d:\xls\books.xlsx"); select max(lenmax) into :maxlen separated by ' ' from connection to Excel ( Select max(len(book)) as lenmax from book1 union select max(len(book)) as lenmax from book2 ); disconnect from Excel; quit; libname xel "d:/xls/books.xlsx"; run;quit; ))); end; set xel.book1(dbsastype=(book="char(&maxlen.)")) xel.book2(dbsastype=(book="char(&maxlen.)")) ; run;quit; libname xel clear; Variables in Creation Order # Variable Type Len Format Informat Label 1 BOOK Char 11 $6. $6. BOOK ... View more

Other ways to present the data Does SAS have a Southern prejudice, New York, Chicago once an no Los Angeles? SASGF 1976-2020 Host Cities Ordered by Most Frequent Locations ============================================================== Orlando, FL + 88 95 02 08 12 San Francisco, CA + 82 89 06 13 New Orleans, LA + 77 83 91 Las Vegas, NV + 78 11 16 Dallas, TX + 87 94 15 Washington, DC + 09 14 Seattle, WA + 03 10 San Antonio, TX + 80 07 Nashville, TN + 90 98 San Diego, CA + 97 Reno, NV + 85 Philadelphia, PA + 05 New York, NY + 93 Montreal, Quebec, CAN + 04 Miami Beach, FL + 99 Long Beach, CA + 01 Lake Buena Vista, FL + 81 Kissimmee, FL + 76 Indianapolis, IN + 00 Honolulu, HI + 92 Hollywood Beach, FL + 84 Clearwater, FL + 79 Chicago, IL + 96 Atlanta, GA + 86 | ---+---------+---------+---------+---------+---------+-- 1970 1980 1990 2000 2010 2020 Year SASGF 1976-2020 Host Cities Ordered by Most Frequent Locations Location Frequency Year ======== ========= === Orlando, FL 5 1988 1995 2002 2008 2012 San Francisco, CA 4 1982 1989 2006 2013 Dallas, TX 3 1987 1994 2015 Las Vegas, NV 3 1978 2011 2016 New Orleans, LA 3 1977 1983 1991 Nashville, TN 2 1990 1998 San Antonio, TX 2 1980 2007 Seattle, WA 2 2003 2010 Washington, DC 2 2009 2014 Atlanta, GA 1 1986 Chicago, IL 1 1996 Clearwater, FL 1 1979 Hollywood Beach, FL 1 1984 Honolulu, HI 1 1992 Indianapolis, IN 1 2000 Kissimmee, FL 1 1976 Lake Buena Vista, FL 1 1981 Long Beach, CA 1 2001 Miami Beach, FL 1 1999 Montreal, Quebec, CAN 1 2004 New York, NY 1 1993 Philadelphia, PA 1 2005 Reno, NV 1 1985 San Diego, CA 1 1997 * to lazy to add attendance; data have; length loc $44; input; pre=substr(put(2017-_n_,4.),3,2); odr=2017-_n_; loc=_infile_; cards4; Las Vegas, NV Dallas, TX Washington, DC San Francisco, CA Orlando, FL Las Vegas, NV Seattle, WA Washington, DC Orlando, FL San Antonio, TX San Francisco, CA Philadelphia, PA Montreal, Quebec, CAN Seattle, WA Orlando, FL Long Beach, CA Indianapolis, IN Miami Beach, FL Nashville, TN San Diego, CA Chicago, IL Orlando, FL Dallas, TX New York, NY Honolulu, HI New Orleans, LA Nashville, TN San Francisco, CA Orlando, FL Dallas, TX Atlanta, GA Reno, NV Hollywood Beach, FL New Orleans, LA San Francisco, CA Lake Buena Vista, FL San Antonio, TX Clearwater, FL Las Vegas, NV New Orleans, LA Kissimmee, FL ;;;; run;quit; proc sql; create table frq as select count(*) as frq ,loc ,odr ,pre from have group by loc order by frq descending, loc, odr ;quit; proc report data=frq nowd list; cols ("SASGF 1976-2020 Host Cities Ordered by Most Frequent Locations" loc frq odr); DEFINE LOC / order FORMAT= $44. WIDTH=34 SPACING=2 LEFT "Location" order=data; DEFINE FRQ / order FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Frqquency" order=data; DEFINE ODR / SUM FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Year" ; RUN;quit; proc freq data=have order=freq ; tables loc / out=frq; run;quit; proc sql; select distinct quote(trim(loc)) into :loc separated by ' ' from frq order by frq ; quit; options ls=90 ps=50; proc plot data=frq; plot loc*odr=' ' $ pre / vaxis=&loc; run;quit; ... View more