Love it ... View more

Macro for using data step to several dataset Not a macro solution. You can deconstruct my algorithm and make it a macro. insired by https://goo.gl/x5t1PT https://communities.sas.com/t5/General-SAS-Programming/Macro-for-using-data-step-to-several-dataset/m-p/348747 HAVE 16 datasets each with three years 2 different variables ============================================================== Up to 40 obs WORK.META total obs=96 Obs YEAR DSN VAR1 VAR2 1 2015 CE67F CE EC data set CE67F with varaibles CE and EC 2 2016 CE67F CE EC 3 2017 CE67F CE EC 4 2015 DF68G DF FD data set DF68G with varaibles Df and FD 5 2016 DF68G DF FD 6 2017 DF68G DF FD 7 2015 EG69H EG GE 8 2016 EG69H EG GE 9 2017 EG69H EG GE Here is two of the 16 datasets look like ======================================== Dateset CE67F Up to 40 obs from CE67F total obs=6 Obs YEAR CE EC Mean 1 2015 40 99 (99 + 78)/2 =88.5 2 2015 71 78 3 2016 81 57 4 2016 37 8 5 2017 5 9 6 2017 52 15 Dataset DF68G Up to 40 obs from DF68G total obs=6 Obs YEAR DF FD 1 2015 18 46 2 2015 27 54 3 2016 2 87 4 2016 93 32 5 2017 29 4 6 2017 31 39 WANT (summarize by dsn and year 6 obs to 3 means) ==== Up to 40 obs from sum32 total obs=6 Obs DSN YEAR INPVAR INPVAR2 VAR1 VAR2 1 CE67F 2015 CE EC 55.5 88.5 (var2=(99 + 78)/2 =88.5) 2 CE67F 2016 CE EC 59.0 32.5 3 CE67F 2017 CE EC 28.5 12.0 4 DF68G 2015 DF FD 22.5 50.0 5 DF68G 2016 DF FD 47.5 59.5 6 DF68G 2017 DF FD 30.0 21.5 WORKING CODE ============ DOSUBL set &dsn(rename=(&var1=var1 &var2=var2)); proc append data=getone base=all32; DOSUBL proc summary data=all32 mean nway; FULL SOLUTION ============= * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; data meta_pre; retain year dsn var1 var2; retain cnt 0; do ltr=67 to 87; cnt=cnt+1; length dsn $5 var1 var2 $2; dsn=cats(byte(ltr),byte(ltr+2),put(ltr,2.),byte(ltr+3)); var1=cats(byte(ltr),byte(ltr+2)); var2=cats(byte(ltr+2),byte(ltr)); do year=2015 to 2017;output;end; dsn=cats(byte(ltr+3),byte(ltr+2),put(ltr,2.),byte(ltr+1)); var1=cats(byte(ltr-1),byte(ltr+2)); var2=cats(byte(ltr-2),byte(ltr)); do year=2015 to 2017;output;end; drop ltr cnt; if cnt=16 then stop; end; run;quit; proc sort data=meta_pre out=meta; by dsn year var1 var2; run;quit; data _null_; set meta; if _n_=1 then call streaminit(123); call symputx('dsn',dsn); call symputx('var1',var1); call symputx('var2',var2); rc=dosubl(' data &dsn; do year=2015,2015,2016,2016,2017,2017; &var1 = int(100*(rand("Uniform"))); &var2 = int(100*(rand("Uniform"))); output; end; '); run;quit; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; proc datasets lib=work; delete all32; run;quit; %symdel dsn var1 var2/ nowarn; data _null_; * put all 16 datasets into all32; do until (eof); set meta end=eof; call symputx('dsn',dsn); call symputx('var1',var1); call symputx('var2',var2); rc=dosubl(' data getone; set &dsn(rename=(&var1=var1 &var2=var2)); dsn=symget("dsn"); inp1=symget("var1"); inp2=symget("var2"); run;quit; proc append data=getone base=all32; run;quit; '); end; if rc ne 0 then do; put "*** proc append failed *** on dsn=" dsn; stop; end; * summarize the big dataset; rc=dosubl(' proc summary data=all32 mean nway; class dsn year inp1 inp2 ; var var1 var2; output out=sum32(drop=_type_ _freq_) mean=; run;quit; '); if rc ne 0 then do; put "*** proc summary failed ***"; stop; end; stop; run;quit; SYMBOLGEN: Macro variable DSN resolves to CE67F SYMBOLGEN: Macro variable VAR1 resolves to CE SYMBOLGEN: Macro variable VAR2 resolves to EC NOTE: There were 6 observations read from the data set WORK.CE67F. NOTE: The data set WORK.GETONE has 6 observations and 6 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 1209.25k OS Memory 18668.00k Timestamp 04/10/2017 08:17:28 PM Step Count 626 Switch Count 0 NOTE: Appending WORK.GETONE to WORK.ALL32. NOTE: There were 6 observations read from the data set WORK.GETONE. NOTE: 6 observations added. NOTE: The data set WORK.ALL32 has 78 observations and 6 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 1209.25k OS Memory 18924.00k Timestamp 04/10/2017 08:17:28 PM Step Count 626 Switch Count 0 ... View more

There are user requiements that would necessitate a grid.   I suggest polling SAS users to determine if a power workstation in combination with a grid and large pipe for downloads might be appropriate.   Pros - You need it because of PII and big data(as I defined above) or big computation user problems.           I have used the grid at petabyte installations and it was the only way to solve some problems in a reasonable time.   Cons - It is complex and if not needed do not get it. Leverage your power workstations so you may not need it.   It is not a business decision it is a sometimes a user requirement   Benchmarks to see if you need it ie power workstation v grid. ... View more

/* T1001420 SAS/R Importing a paradox database into SAS SAS did a better job than R. Neither R nor SAS could convert Paradox 7 tables? Of the Paradox 4 and 5 SAS read 4 and R read 3 There seem to be a lot of issues,especially with Paradox 7. Some data types have issues in versions 5 and 6? SAS/R Importing a paradox database into SAS inspired by https://goo.gl/f0UGW7 https://communities.sas.com/t5/Base-SAS-Programming/Importing-a-paradox-db-file-into-SAS/m-p/324832/highlight/false#M72138 SAS did a better job than R. Neither R nor SAS could convert Paradox 7 tables? Of the Paradox 4 and 5 SAS read 4 and R read 3 * you can get a sample paradox database here; * these are good samples and have many variable types; http://www.sportamok.com/development/download; * Roland Rashleigh-Berry directory code, may he RIP; * the old guard is dying off; * don't forget the blank after db; * put the members of directory d:\db in macro var dbs; * macros on end; %let dbs= %utl_dir(d:\db\*.db ); %put &=dbs; DBS=biolife.db COUNTRIES.DB country.db events.db FILMS.DB venues.db Uppercase Paradox 7 tables could not be read by SAS or R but Stattransfer could read them. HAVE A PARADOX DATABASE WITH THESE TABLES (Paradox 4, 5 and 7) =============================================================== biolife.db country.db * R could not read this one but SAS could events.db venues.db WANT SAS DATSETS ================ BIOLIFE.SAS7BDAT COUNTRY.SAS7BDAT EVENTS.SAS7BDAT VENUES.SAS7BDAT SAS SOLUTION (cannot read Paradox 7 tables, however Stat Transfer can) options validvarname=upcase;; data _null_; tables= "%utl_dir(d:\db\*.db )"; put tables=; do tbl=1 to countc(tables,'.'); table=scan(scan(tables,tbl,' '),1,'.'); * PARADOX 7; if upcase(table) not in ('COUNTRIES', 'FILMS', 'CARS' 'RESTTEMP') then do; call symputx('table',table); put table=; rc=dosubl(' proc import out=work.&table file="d:/db/&table..db" dbms=db replace; run;quit; '); end; end; run;quit; * As soon as R implements 'write_sas' this code will become very simple - the save will rePlaced by something like: for (table in tables) {write_sas(assign(table,sqlFetch(myDB, table),paste("d:/db/",table,".sas7bdat")}; However stattransfer and SAS seem to do a better job; Howver it is rather complex now; * I don't have a 64 bit driver so I need to use the 32bit version of R, note R comes with both versions; * this works; * you may need 32bit SAS. I used 32bit R; * create R dataframe from db table; %macro todb(dummy); %let dbs=biolife.db country.db event.db venues.db; %do tbl=1 %to %sysfunc(countc(&dbs,%str(.))); %let table=%sysfunc(strip(%scan(%scan(&dbs.,&tbl.,%str( )),1,%str(.)))); %put &=table.; /* %let table=events; */ * you need to use double quotes so macros and macro variables are resolved; %utl_submit_r32(" source('C:/Program Files/R/R-3.3.2/etc/Rprofile.site', echo=T); library(RODBC); library(sqldf); '&table'; myDB<-odbcDriverConnect('Driver={Microsoft Paradox Driver (*.db )};DriverID=538; Fil=Paradox 5.X;DefaultDir=d:/db/;Dbq=d:/db/;CollatingSequence=ASCII'); '&table'; &table <- sqlFetch(myDB, '&table.'); '&table'; save(&table,file='d:/rds/&table..rda', compress = FALSE); &table; close(myDB); "); %utl_submit_wps64(" options set=R_HOME 'C:/Program Files/R/R-3.3.2'; libname wrk '%sysfunc(pathname(work))'; proc r; submit; source('C:/Program Files/R/R-3.3.2/etc/Rprofile.site', echo=T); load(file = 'd:/rds/&table..rda'); &table.; endsubmit; import r=&table. data=wrk.&table.; run;quit; "); %end; %mend todb; %todb; ... View more

Export to .csv value comes in a separate line see https://goo.gl/ZIZI9A https://communities.sas.com/t5/Base-SAS-Programming/Export-to-csv-value-comes-in-a-separate-line/m-p/348823 I assume excel was the source not your SAS dataset? It is always best to work with data in its original format. You can translate the passthru query below to native SAS and use your SAS daatset as input it should fix your 'alt-enters'. You can solve this without the excel source but I thought I would show users how to fix the issue on the excel side. The SAS dataset probably came from excel? You probably gave carriage-returns<alt-enter>, '0D'x, in your data. It is best to fix this on the excel side. I am going to assume you have the excel file; HAVE ==== +--------------------------------------------------------------+ | A ] | B | C | D | +--------------------------------------------------------------+ 1 | ID | STATE | ACTION | COMMENT | +------------+------------+------------+-----------------------+ 2 | 123 | NY | 458425 | Good | +------------+------------+------------+-----------------------+ 3 | 234 | TX | 2565325 | Moderate<alt-enter> | | | | | But<alt-enter> | | | | | Liveable | +------------+------------+------------+-----------------------+ 4 | 543 | CA | 526547 | Not<alt-enter> | | | | | Bad | +------------+------------+------------+-----------------------+ As a side note here is what a the csv looks like with the alt enters. COMMENT,ID,STATE,ACTIONG ood,123,NY,458425 Moderate but Liveable",234,TX,256532 Not bad",543,CA,526547 WANT SAS dataset with spaces for alt_enter =========================================== Up to 40 obs WORK.WANT total obs=3 Obs ID STATE ACTION COMMENT_FIX 1 123 NY 458425 Good 2 234 TX 256532 Moderate but Liveable 3 543 CA 526547 Not bad WORKING CODE ============ Passthru to excel (replace alt-enter with space Replace(comment,chr(13),chr(32)) as comment_fix * _ _ _ _ __ ___ __ _| | _____ _ __ _ __ ___ | |__ __ _| |_____ __ | '_ ` _ \ / _` | |/ / _ \_____| '_ \| '__/ _ \| '_ \ ____\ \/ / / __\ \/ / | | | | | | (_| | < __/_____| |_) | | | (_) | |_) |_____> <| \__ \> < |_| |_| |_|\__,_|_|\_\___| | .__/|_| \___/|_.__/ /_/\_\_|___/_/\_\ |_| ; %utlfkil(d:/xls/have.xlsx); libname xel "d:/xls/have.xlsx"; data xel.have; informat comment $32.; input id State$ Comment$ Action$ ; comment=tranwrd(comment,'z','0D'x); cards4; 123 NY Good 458425 234 TX ModeratezbutzLiveable 256532 543 CA Notzbad 526547 ;;;; run;quit; libname xel clear; * _ _ __ _ _ __ __ _ ___ ___| |_| |__ _ __ _ _ / _(_)_ __ | '_ \ / _` / __/ __| __| '_ \| '__| | | |_____| |_| \ \/ / | |_) | (_| \__ \__ \ |_| | | | | | |_| |_____| _| |> < | .__/ \__,_|___/___/\__|_| |_|_| \__,_| |_| |_/_/\_\ |_| ; proc sql dquote=ansi; connect to excel (Path="d:\xls\have.xlsx" mixed=yes); create table want as select * from connection to Excel ( Select id ,state ,action ,Replace(comment,chr(13),chr(32)) as comment_fix from [have$] ) ; disconnect from Excel; Quit; 2441 proc sql dquote=ansi; 2442 connect to excel (Path="d:\xls\have.xlsx" mixed=yes); NOTE: Data source is connected in READ ONLY mode. 2443 create 2444 table want as 2445 select * from connection to Excel 2446 ( Select 2447 id 2448 ,state 2449 ,action 2450 ,Replace(comment,chr(13),chr(32)) as comment_fix 2451 from 2452 [have$] 2453 ) ; NOTE: Table WORK."WANT" created, with 3 rows and 4 columns. 2454 disconnect from Excel; 2455 Quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.01 seconds ... View more

SAS Forum: Splitting Columns formatting problem above this message https://goo.gl/Zp9ntH https://communities.sas.com/t5/Base-SAS-Programming/Splitting-columns/m-p/348756 HAVE ==== Up to 40 obs WORK.HAVE total obs=7 APPOINTMENT_ NUMBER_ Obs EMPLOYEEID DATE CLASS LEVEL SALARY APPOINTMENTS 1 1 1 AS 2 40000 2 2 1 2 AS 3 50000 2 3 2 1 EC 6 100000 1 4 3 1 CS 1 50000 3 5 3 2 CS 2 60000 3 6 3 3 CS 3 80000 3 7 4 1 EX 3 150000 1 WANT ==== Up to 40 obs WORK.WANT total obs=4 APPOINTMENT EMPLOYEEID DATE_1 DATE_2 DATE_3 CLASS_1 CLASS_2 CLASS_3 LEVEL_1 LEVEL_2 LEVEL_3 SALARY_1 SALARY_2 SALARY_3 1 1 2 . AS AS 2 3 . 40000 50000 . 2 1 . . EC 6 . . 100000 . . 3 1 2 3 CS CS CS 1 2 3 50000 60000 80000 4 1 . . EX 3 . . 150000 . . WORKING CODE ============ If uncomfortable or if this is production you can move the DOSUBL out DOSUBL proc sql; select max(grpcnt) into :grpCnt separated by "" from (select count(employeeid) as grpcnt from have group by employeeID) ;quit; array cls[&grpcnt] $ Class_1 - Class_&grpCnt; * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; data have; input EmployeeID Appointment_date Class $ Level Salary Number_appointments; cards4; 1 1 AS 2 40000 2 1 2 AS 3 50000 2 2 1 EC 6 100000 1 3 1 CS 1 50000 3 3 2 CS 2 60000 3 3 3 CS 3 80000 3 4 1 EX 3 150000 1 ;;;; run;quit; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %symdel rc grpcnt / nowarn; data want; * meta data; if _n_ =0 then do; %let rc=%sysfunc(dosubl(' proc sql; select max(grpcnt) into :grpCnt separated by "" from (select count(employeeid) as grpcnt from have group by employeeID) ;quit; ')); retain rc &rc; end; * if sql fails; if rc ne 0 then stop; retain cnt 0 employeeid Appointment_date_1-Appointment_date_&grpCnt Class_1 - Class_&grpCnt Level_1 - Level_&grpCnt Salary_1 - Salary_&grpCnt; ; set have; by employeeid; array dte[&grpcnt] Appointment_date_1-Appointment_date_&grpCnt; array cls[&grpcnt] $ Class_1 - Class_&grpCnt; array lvl[&grpcnt] Level_1 - Level_&grpCnt; array pay[&grpcnt] Salary_1 - Salary_&grpCnt; cnt=cnt+1; dte[cnt]=Appointment_date; cls[cnt]=Class; lvl[cnt]=Level; pay[cnt]=Salary; if last.employeeid then do; output; call missing(of _all_); cnt=0; rc=0; end; drop rc cnt Appointment_date Class Level Salary Number_appointments; run;quit; ... View more

SAS Forum: Splitting Columns this message https://goo.gl/Zp9ntH https://communities.sas.com/t5/Base-SAS-Programming/Splitting-columns/m-p/348756 HAVE ==== Up to 40 obs WORK.HAVE total obs=7 APPOINTMENT_ NUMBER_ Obs EMPLOYEEID DATE CLASS LEVEL SALARY APPOINTMENTS 1 1 1 AS 2 40000 2 2 1 2 AS 3 50000 2 3 2 1 EC 6 100000 1 4 3 1 CS 1 50000 3 5 3 2 CS 2 60000 3 6 3 3 CS 3 80000 3 7 4 1 EX 3 150000 1 WANT ==== Up to 40 obs WORK.WANT total obs=4 APPOINTMENT EMPLOYEEID DATE_1 DATE_2 DATE_3 CLASS_1 CLASS_2 CLASS_3 LEVEL_1 LEVEL_2 LEVEL_3 SALARY_1 SALARY_2 SALARY_3 1 1 2 . AS AS 2 3 . 40000 50000 . 2 1 . . EC 6 . . 100000 . . 3 1 2 3 CS CS CS 1 2 3 50000 60000 80000 4 1 . . EX 3 . . 150000 . . WORKING CODE ============ If uncomfortable or if this is production you can move the DOSUBL out DOSUBL proc sql; select max(grpcnt) into :grpCnt separated by "" from (select count(employeeid) as grpcnt from have group by employeeID) ;quit; array cls[&grpcnt] $ Class_1 - Class_&grpCnt; * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; data have; input EmployeeID Appointment_date Class $ Level Salary Number_appointments; cards4; 1 1 AS 2 40000 2 1 2 AS 3 50000 2 2 1 EC 6 100000 1 3 1 CS 1 50000 3 3 2 CS 2 60000 3 3 3 CS 3 80000 3 4 1 EX 3 150000 1 ;;;; run;quit; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %symdel rc grpcnt / nowarn; data want; * meta data; if _n_ =0 then do; %let rc=%sysfunc(dosubl(' proc sql; select max(grpcnt) into :grpCnt separated by "" from (select count(employeeid) as grpcnt from have group by employeeID) ;quit; ')); retain rc &rc; end; * if sql fails; if rc ne 0 then stop; retain cnt 0 employeeid Appointment_date_1-Appointment_date_&grpCnt Class_1 - Class_&grpCnt Level_1 - Level_&grpCnt Salary_1 - Salary_&grpCnt; ; set have; by employeeid; array dte[&grpcnt] Appointment_date_1-Appointment_date_&grpCnt; array cls[&grpcnt] $ Class_1 - Class_&grpCnt; array lvl[&grpcnt] Level_1 - Level_&grpCnt; array pay[&grpcnt] Salary_1 - Salary_&grpCnt; cnt=cnt+1; dte[cnt]=Appointment_date; cls[cnt]=Class; lvl[cnt]=Level; pay[cnt]=Salary; if last.employeeid then do; output; call missing(of _all_); cnt=0; rc=0; end; drop rc cnt Appointment_date Class Level Salary Number_appointments; run;quit; ... View more

Just my 2 cents, since you asked:   I am not a SAS admistator, but in situations where you need over 32 cores for over 8 hrs or a single table is over 1TB it is had to beat the grid. The grid also may be needed if PII is a concern.   However new relatively inexpensive off lease power systems(old servers can be used as power workstations) often are musf faster, A lot depends on the server load and whether data can be de-identified. Workstations are more secure when only de-identified data is used,rather than on the sever where the user parks identifiable data in his account. SSD's and memory prices are collapsing.   It is somewhat strange that adminstartors love 10 second jobs with less than a 1GB of ram or disk space. EG especially under VM often limits users to 4 cores and or 4gb ram and or 100gb of disk. This is opposite of purpose of large servers? ... View more

Proc report with different titles, fornotes and Pag 1 of <n> ODS PDF. Write a line of text if condition is satisfied I believe the solution below will also work for the pdf destination. this message https://goo.gl/ZZLhTv https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-PDF-Write-a-line-of-text-if-condition-is-satisfied/m-p/348526 see for output https://www.dropbox.com/s/z60z04j8rzplfbn/difTytFot.rtf?dl=0 This is a dumb example that can be done with a macro however the meta data could involve dozens of rows. You can convert this to amacro quite easily. HAVE ( Meta data and sashelp.class ) ===================================== Up to 40 obs WORK.META total obs=2 PAT1 TYT1 FOT1 M Invoice 2378675 for Rural Participants see Protocol 1013.6(Oncology) F Invoice 288675 for Urban Participants see Protocol 10883.6(Analgesics) Up to 40 obs from sashelp.class total obs=19 Obs NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69.0 112.5 2 Alice F 13 56.5 84.0 3 Barbara F 13 65.3 98.0 4 Carol F 14 62.8 102.5 5 Henry M 14 63.5 102.5 6 James M 12 57.3 83.0 ... WANT (two page report with different title and footnotes) ========================================================== PAGE 1 Student Clinical Trial Males Invoice 2378675 for Rural Participants NAME SEX AGE HEIGHT WEIGHT Alfred M 14 69 112.5 Henry M 14 63.5 102.5 James M 12 57.3 83 Jeffrey M 13 62.5 84 John M 12 59 99.5 Philip M 16 72 150 Robert M 12 64.8 128 Ronald M 15 67 133 Thomas M 11 57.5 85 William M 15 66.5 112 Page 1 of 2 Program: c:/utl/dm.sas_09APR17 Log: c:/utl/dm.log_09APR17 see Protocol 1013.6(Oncology) PAGE 2 Student Clinical Trial Females Invoice 288675 for Urban Participants NAME SEX AGE HEIGHT WEIGHT Alice F 13 56.5 84 Barbara F 13 65.3 98 Carol F 14 62.8 102.5 Jane F 12 59.8 84.5 Janet F 15 62.5 112.5 Joyce F 11 51.3 50.5 Judy F 14 64.3 90 Louise F 12 56.3 77 Mary F 15 66.5 112 Page 2 of 2 Program: c:/utl/dm.sas_09APR17 Log: c:/utl/dm.log_09APR17 see Protocol 10883.6(Analgesics) WORKING CODE ============ array pat[&sqlobs] $8 (&pat.); array tyt[&sqlobs] $64 (&tyt.); array fot[&sqlobs] $64 (&fot.); do i=1 to &sqlobs; rc=dosubl( FULL SOLUTION ============= * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; %utl_rtflan100; %let pgm=utl_diff_tyt_rtf; %let outfile=d:/rtf/difTytFot.rtf; %utl_rtflan100; data meta; pge1='1'; pat1='M'; tyt1='Invoice 2378675 for Rural Partipicpants '; fot1='see Protocol 1013.6(Oncology) '; output; pge1='2'; pat1='F'; tyt1='Invoice 288675 for Urban Partipicpants '; fot1='see Protocol 10883.6(Analgesics) '; output; run;quit; options nodate nonumber orientation=portrait; title;footnote; ods escapechar='^'; ods listing close; ods rtf file="&outfile" style=utl_rtflan100 notoc_data; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %symdel pge pat tyt fot/ nowarn; data _null_; * get meta data; if _n_=0 then do; rc=%sysfunc(dosubl(' proc sql noprint ; select quote(pat1) ,quote(tyt1) ,quote(fot1) into :pat separated by "," ,:tyt separated by "," ,:fot separated by "," from meta ;quit; ')); end; array pat[&sqlobs] $8 (&pat.); array tyt[&sqlobs] $64 (&tyt.); array fot[&sqlobs] $64 (&fot.); do i=1 to &sqlobs; call symputx('pat',pat[i]); call symputx('tyt',tyt[i]); call symputx('fot',fot[i]); call symputx('pge',put(i,1.)); rc=dosubl(' ods rtf prepage= "^S={outputwidth=100% just=c font_size=11pt font_face=arial} {Student Clinical Trial Gebder=&pat.}^{newline}{&tyt}"; proc report data=sashelp.class (where=(sex="&pat")) nowd split="#" missing; run;quit; ods rtf text="^S={outputwidth=100% just=r font_size=9pt} Page &pge of &sqlobs"; ods rtf text="^S={outputwidth=100% just=l font_size=8pt font_style=italic} {Program: c:/utl/dm.sas_&sysdate}"; ods rtf text="^S={outputwidth=100% just=l font_size=8pt font_style=italic} {Log: c:/utl/dm.log_&sysdate}"; ods rtf text="^S={outputwidth=100% just=l font_size=8pt font_style=italic} {&fot}"; '); end; stop; run;quit; ods rtf close; ods listing; * _ _ _ __ _ _ ___ ___ _ _| |_| | _ __| |_ / _| | __ _ _ __ / |/ _ \ / _ \ | | | | __| |_____| '__| __| |_| |/ _` | '_ \| | | | | | | | | |_| | |_| |_____| | | |_| _| | (_| | | | | | |_| | |_| | \__,_|\__|_| |_| \__|_| |_|\__,_|_| |_|_|\___/ \___/ ; %Macro utl_rtflan100 ( style=utl_rtflan100, frame=box, rules=groups, bottommargin=1.0in, topmargin=1.5in, rightmargin=1.0in, cellheight=10pt, cellpadding = 7, cellspacing = 3, leftmargin=.75in, borderwidth = 1 ) / Des="SAS Rtf Template for CompuCraft"; options orientation=landscape;run;quit; ods path work.templat(update) sasuser.templat(update) sashelp.tmplmst(read); Proc Template; define style &Style; parent=styles.rtf; replace body from Document / protectspecialchars=off asis=on bottommargin=&bottommargin topmargin =&topmargin rightmargin =&rightmargin leftmargin =&leftmargin ; replace color_list / 'link' = blue 'bgH' = _undef_ 'fg' = black 'bg' = _undef_; replace fonts / 'TitleFont2' = ("Arial, Helvetica, Helv",11pt,Bold) 'TitleFont' = ("Arial, Helvetica, Helv",11pt,Bold) 'HeadingFont' = ("Arial, Helvetica, Helv",10pt) 'HeadingEmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic) 'StrongFont' = ("Arial, Helvetica, Helv",10pt,Bold) 'EmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic) 'FixedFont' = ("Courier New, Courier",9pt) 'FixedEmphasisFont' = ("Courier New, Courier",9pt,Italic) 'FixedStrongFont' = ("Courier New, Courier",9pt,Bold) 'FixedHeadingFont' = ("Courier New, Courier",9pt,Bold) 'BatchFixedFont' = ("Courier New, Courier",7pt) 'docFont' = ("Arial, Helvetica, Helv",10pt) 'FootFont' = ("Arial, Helvetica, Helv", 9pt) 'StrongFootFont' = ("Arial, Helvetica, Helv",8pt,Bold) 'EmphasisFootFont' = ("Arial, Helvetica, Helv",8pt,Italic) 'FixedFootFont' = ("Courier New, Courier",8pt) 'FixedEmphasisFootFont'= ("Courier New, Courier",8pt,Italic) 'FixedStrongFootFont' = ("Courier New, Courier",7pt,Bold); replace GraphFonts / 'GraphDataFont' = ("Arial, Helvetica, Helv",8pt) 'GraphAnnoFont' = ("Arial, Helvetica, Helv",8pt) 'GraphValueFont' = ("Arial, Helvetica, Helv",10pt) 'GraphUnicodeFont' = ("Arial, Helvetica, Helv",10pt) 'GraphLabelFont' = ("Arial, Helvetica, Helv",10pt,Bold) 'GraphLabel2Font' = ("Arial, Helvetica, Helv",10pt,Bold) 'GraphFootnoteFont' = ("Arial, Helvetica, Helv",8pt) 'GraphTitle1Font' = ("Arial, Helvetica, Helv",11pt,Bold) 'GraphTitleFont' = ("Arial, Helvetica, Helv",11pt,Bold); style table from table / outputwidth=100% protectspecialchars=off asis=on background = colors('tablebg') frame=&frame rules=&rules cellheight = &cellheight cellpadding = &cellpadding cellspacing = &cellspacing bordercolor = colors('tableborder') borderwidth = &borderwidth; replace Footer from HeadersAndFooters / font = fonts('FootFont') just=left asis=on protectspecialchars=off ; replace FooterFixed from Footer / font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off; replace FooterEmpty from Footer / font = fonts('FootFont') just=left asis=on protectspecialchars=off; replace FooterEmphasis from Footer / font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off; replace FooterEmphasisFixed from FooterEmphasis / font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off; replace FooterStrong from Footer / font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off; replace FooterStrongFixed from FooterStrong / font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off; replace RowFooter from Footer / font = fonts('FootFont') asis=on protectspecialchars=off just=left; replace RowFooterFixed from RowFooter / font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off; replace RowFooterEmpty from RowFooter / font = fonts('FootFont') just=left asis=on protectspecialchars=off; replace RowFooterEmphasis from RowFooter / font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off; replace RowFooterEmphasisFixed from RowFooterEmphasis / font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off; replace RowFooterStrong from RowFooter / font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off; replace RowFooterStrongFixed from RowFooterStrong / font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off; replace SystemFooter from TitlesAndFooters / asis=on protectspecialchars=off just=left; end; run; quit; %Mend utl_rtflan100; *_ | | ___ __ _ | |/ _ \ / _` | | | (_) | (_| | |_|\___/ \__, | |___/ ; 4348 %symdel pge pat tyt fot/ nowarn; 4349 data _null_; 4350 * get meta data; 4351 if _n_=0 then do; 4352 rc=%sysfunc(dosubl(' 4353 proc sql noprint ; 4354 select 4355 quote(pat1) 4356 ,quote(tyt1) 4357 ,quote(fot1) 4358 into 4359 :pat separated by "," 4360 ,:tyt separated by "," NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 3717.31k OS Memory 19696.00k Timestamp 04/09/2017 03:25:39 PM Step Count 235 Switch Count 0 4361 ,:fot separated by "," 4362 from 4363 meta 4364 ;quit; 4365 ')); 4366 end; 4367 array pat[&sqlobs] $8 (&pat.); SYMBOLGEN: Macro variable SQLOBS resolves to 2 SYMBOLGEN: Macro variable PAT resolves to "M","F" 4368 array tyt[&sqlobs] $64 (&tyt.); SYMBOLGEN: Macro variable SQLOBS resolves to 2 SYMBOLGEN: Macro variable TYT resolves to "Invoice 2378675 for Rural Partipicpants ","Invoice 288675 for Urban Partipicpants " 4369 array fot[&sqlobs] $64 (&fot.); SYMBOLGEN: Macro variable SQLOBS resolves to 2 SYMBOLGEN: Macro variable FOT resolves to "see Protocol 1013.6(Oncology) ","see Protocol 10883.6(Analgesics) " 4370 do i=1 to &sqlobs; SYMBOLGEN: Macro variable SQLOBS resolves to 2 4371 call symputx('pat',pat[i]); 4372 call symputx('tyt',tyt[i]); 4373 call symputx('fot',fot[i]); 4374 call symputx('pge',put(i,1.)); 4375 rc=dosubl(' 4376 ods rtf prepage= 4377 "^S={outputwidth=100% just=c font_size=11pt 4377! font_face=arial} {Student Clinical Trial 4377! Gebder=&pat.}^{newline}{&tyt}"; 4378 proc report data=sashelp.class (where=(sex="&pat")) 4378! nowd split="#" missing; 4379 run;quit; 4380 ods rtf text="^S={outputwidth=100% just=r 4380! font_size=9pt} Page &pge of &sqlobs"; 4381 ods rtf text="^S={outputwidth=100% just=l 4381! font_size=8pt font_style=italic} {Program: 4381! c:/utl/dm.sas_&sysdate}"; 4382 ods rtf text="^S={outputwidth=100% just=l 4382! font_size=8pt font_style=italic} {Log: 4382! c:/utl/dm.log_&sysdate}"; 4383 ods rtf text="^S={outputwidth=100% just=l 4383! font_size=8pt font_style=italic} {&fot}"; 4384 '); 4385 end; 4386 stop; 4387 run; SYMBOLGEN: Macro variable PAT resolves to M SYMBOLGEN: Macro variable TYT resolves to Invoice 2378675 for Rural Partipicpants NOTE: Writing RTF Body file: sasrtf.rtf SYMBOLGEN: Macro variable PAT resolves to M 4387! quit; NOTE: There were 10 observations read from the data set SASHELP.CLASS. WHERE sex='M'; NOTE: PROCEDURE REPORT used (Total process time): real time 0.10 seconds user cpu time 0.03 seconds system cpu time 0.07 seconds memory 10927.62k OS Memory 24324.00k Timestamp 04/09/2017 03:25:39 PM Step Count 235 Switch Count 0 SYMBOLGEN: Macro variable PGE resolves to 1 SYMBOLGEN: Macro variable SQLOBS resolves to 2 SYMBOLGEN: Macro variable SYSDATE resolves to 09APR17 SYMBOLGEN: Macro variable SYSDATE resolves to 09APR17 SYMBOLGEN: Macro variable FOT resolves to see Protocol 1013.6(Oncology) SYMBOLGEN: Macro variable PAT resolves to F SYMBOLGEN: Macro variable TYT resolves to Invoice 288675 for Urban Partipicpants SYMBOLGEN: Macro variable PAT resolves to F NOTE: There were 9 observations read from the data set SASHELP.CLASS. WHERE sex='F'; NOTE: PROCEDURE REPORT used (Total process time): real time 0.06 seconds user cpu time 0.01 seconds system cpu time 0.03 seconds memory 11190.09k OS Memory 24836.00k Timestamp 04/09/2017 03:25:40 PM Step Count 235 Switch Count 0 SYMBOLGEN: Macro variable PGE resolves to 2 SYMBOLGEN: Macro variable SQLOBS resolves to 2 SYMBOLGEN: Macro variable SYSDATE resolves to 09APR17 SYMBOLGEN: Macro variable SYSDATE resolves to 09APR17 SYMBOLGEN: Macro variable FOT resolves to see Protocol 10883.6(Analgesics) NOTE: DATA statement used (Total process time): real time 0.59 seconds user cpu time 0.17 seconds system cpu time 0.29 seconds memory 11190.09k OS Memory 24836.00k Timestamp 04/09/2017 03:25:40 PM Step Count 235 Switch Count 3 4388 ods rtf close; 4389 ods listing; ... View more

Importing the most recent excel workbook in a directory I have a unix version but if I remember, the solution has SAS call Perl. Dates have mutiple formates in unis 'ls -l' inspred by https://goo.gl/koClI1 https://communities.sas.com/t5/Base-SAS-Programming/Proc-Import/m-p/348515 If you find the code below too complex just extract the datasteps and remove the DOSUBLs and the parent dataset HAVE (directory of d:/xls has following files) ==== 4/09/2017 01:41 PM 7,827 BankData123400.xlsx ==> most recent 04/09/2017 12:37 PM 47,673 BankData123456.xlsx 04/09/2017 11:20 AM 8,143 BankData123478.xlsx 04/09/2017 11:22 AM 8,144 BankData123488.xlsx Here is what is in the most recent workbook d:/xls/BankData123400.xlsx +------+-----------+ | | A | +------+-----------+ | | | | 1 | ELIGIBLE | | 2 | TRUE | | 3 | FALSE | | 4 | TRUE | | 5 | FALSE | | 6 | TRUE | | ... | ... | +------------------+ [SHEET1] WANT (dataset work.BankData123400) d:\xls\BankData123400.xlsx 09APR2017:13:41:00 Up to 40 obs from BankData123400 total obs=5 Obs ELIGIBLE 1 TRUE 2 FALS 3 TRUE 4 FALS 5 TRUE WORKING CODE ============ libname xel '&pth.&names.'; data %scan(&names,1,'.'); set xel.'sheet1$'n; run;quit; FULL SOLUTION ============= %symdel creation names pth; proc datasets lib=work kill; run;quit; libname xel clear; %let pth=d:\xls\; data _null_; if _n_=0 then do; rc=%sysfunc(dosubl(' filename pipes pipe "DIR /TW /Q /S &pth."; data __dir(where=(size>0 and index(name,'.xls')>0)); infile pipes truncover end=eof; do until(eof); input @; if index(_infile_,"Directory of")>0 then input @ "Directory of " dir $128.; else do; input creation ?? mdyampm18. @; if not missing(creation) then do; input @26 size:??comma16. @40 owner:$32. @63 name:$64.; output; end; else input; end; end; stop; run;quit; data _null_; length nam $64; retain dtemke . nam; do until (dne); set __dir end=dne; if creation > dtemke then do; put _all_; dtemke=creation; nam = name; end; end; call symputx("creation",put(dtemke,datetime23.)); call symputx("names",nam); stop; run;quit; ')); end; put "&pth &creation &names "; /* d:\xls\BankData123400.xlsx 09APR2017:13:41:00 */ rc=dosubl(" libname xel '&pth.&names.'; data %scan(&names,1,'.'); set xel.'sheet1$'n; run;quit; "); run;quit; Libref WORK Engine V9 Physical Name e:\saswork\wrk\_TD4456_BEAST_ Filename e:\saswork\wrk\_TD4456_BEAST_ Member Obs, Entries File # Name Type or Indexes Vars Label Size Last Modified 1 BANKDATA123400 DATA 5 1 131072 04/09/2017 13:51:13 2 SASGOPT CATALOG 0 5120 04/09/2017 12:24:01 3 SASMACR CATALOG 63 5120 04/09/2017 12:20:41 4 __DIR DATA 4 5 131072 04/09/2017 13:51:13 NOTE: Deleting WORK.BANKDATA123400 (memtype=DATA). NOTE: Deleting WORK.SASGOPT (memtype=CATALOG). NOTE: File WORK.SASGOPT (memtype=CATALOG) cannot be deleted because it is in use. NOTE: Deleting WORK.SASMACR (memtype=CATALOG). NOTE: File WORK.SASMACR (memtype=CATALOG) cannot be deleted because it is in use. NOTE: Deleting WORK.__DIR (memtype=DATA). 2041 run; 2041! quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.02 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 302.46k OS Memory 14316.00k Timestamp 04/09/2017 01:52:11 PM Step Count 100 Switch Count 0 2042 libname xel clear; WARNING: Libref XEL is not assigned. 2043 %let pth=d:\xls\; 2044 data _null_; 2045 if _n_=0 then do; 2046 rc=%sysfunc(dosubl(' 2047 filename pipes pipe "DIR /TW /Q /S &pth."; 2048 data __dir(where=(size>0 and index(name,'.xls')>0)); 2049 infile pipes truncover end=eof; 2050 do until(eof); 2051 input @; 2052 if index(_infile_,"Directory of")>0 then input @ "Directory of " dir $128.; 2053 else do; 2054 input creation ?? mdyampm18. @; 2055 if not missing(creation) then do; 2056 input @26 size:??comma16. @40 owner:$32. @63 name:$64.; 2057 output; 2058 end; 2059 else input; 2060 end; 2061 end; 2062 stop; 2063 run;quit; 2064 data _null_; 2065 length nam $64; 2066 retain dtemke . nam; 2067 do until (dne); 2068 set __dir end=dne; 2069 if creation > dtemke then do; 2070 put _all_; 2071 dtemke=creation; 2072 nam = name; 2073 end; 2074 end; SYMBOLGEN: Macro variable PTH resolves to d:\xls\ NOTE: The infile PIPES is: Unnamed Pipe Access Device, PROCESS=DIR /TW /Q /S d:\xls\,RECFM=V, LRECL=384 NOTE: 16 records were read from the infile PIPES. The minimum record length was 0. The maximum record length was 81. NOTE: The data set WORK.__DIR has 4 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 874.25k OS Memory 14316.00k Timestamp 04/09/2017 01:52:12 PM Step Count 101 Switch Count 0 NAM= DTEMKE=. DNE=0 DIR=d:\xls CREATION=1807364460 SIZE=7827 OWNER=BEAST\beast NAME=BankData123400.xlsx _ERROR_=0 _N_=1 NOTE: There were 4 observations read from the data set WORK.__DIR. 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 874.25k OS Memory 14316.00k Timestamp 04/09/2017 01:52:12 PM Step Count 101 Switch Count 0 2075 call symputx("creation",put(dtemke,datetime23.)); 2076 call symputx("names",nam); 2077 stop; 2078 run;quit; 2079 ')); 2080 end; SYMBOLGEN: Macro variable PTH resolves to d:\xls\ SYMBOLGEN: Macro variable CREATION resolves to 09APR2017:13:41:00 SYMBOLGEN: Macro variable NAMES resolves to BankData123400.xlsx 2081 put "&pth &creation &names "; 2082 /* 2083 d:\xls\BankData123400.xlsx 09APR2017:13:41:00 2084 */ 2085 rc=dosubl(" 2086 libname xel '&pth.&names.'; SYMBOLGEN: Macro variable PTH resolves to d:\xls\ SYMBOLGEN: Macro variable NAMES resolves to BankData123400.xlsx 2087 data %scan(&names,1,'.'); SYMBOLGEN: Macro variable NAMES resolves to BankData123400.xlsx 2088 set xel.'sheet1$'n; 2089 run;quit; 2090 "); 2091 run; d:\xls\ 09APR2017:13:41:00 BankData123400.xlsx NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:\xls\BankData123400.xlsx NOTE: There were 5 observations read from the data set XEL.'sheet1$'n. NOTE: The data set WORK.BANKDATA123400 has 5 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1067.18k OS Memory 14316.00k Timestamp 04/09/2017 01:52:12 PM Step Count 101 Switch Count 0 NOTE: DATA statement used (Total process time): real time 0.40 seconds user cpu time 0.09 seconds system cpu time 0.20 seconds memory 1067.18k OS Memory 14316.00k Timestamp 04/09/2017 01:52:12 PM Step Count 101 Switch Count 3 2091! quit; SYMBOLGEN: Macro variable PGM resolves to utl_select_most_recent_file SYMBOLGEN: Macro variable PGM resolves to utl_select_most_recent_file SYMBOLGEN: Macro variable _Q resolves to 44441 SYMBOLGEN: Macro variable _Q resolves to 44441 ... View more