If you have IML/R interface you can use RODBC. I think the 32bit driver here handles both 32 an64 bit access. I use WPS but you can use IML/R %utl_submit_wps64(' options set=R_HOME "C:/Program Files/R/R-3.3.1"; libname wrk "%sysfunc(pathname(work))"; proc r; submit; library(RODBC); myDB<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=d:/mdb/HelpfulContacts.mdb"); mtcarsmdb<-sqlQuery(myDB, paste("select * from mtcars")); mtcarsmdb; close(myDB); endsubmit; import r=mtcarsmdb data=wrk.mtcarsmdb; run;quit; '); ... View more

Import ACCESS to SAS 9.4 issue this message https://goo.gl/KgY15M https://communities.sas.com/t5/Base-SAS-Programming/Import-ACCESS-to-SAS-9-4-issue/m-p/347889 HAVE ==== sas has a Access tabe at C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb Libref MDB Engine ACCESS Physical Name C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb User Admin DBMS Member Member # Name Type Vars Label Type 1 Customers DATA 10 TABLE 2 Employees DATA 11 TABLE 3 Invoice DATA 8 TABLE 4 Orders DATA 10 TABLE WANT ==== Up to 40 obs WORK.INVOICE total obs=17 AMOUNT_BILLED_ AMOUNT_ INVOICE_ BILLED_ IN_LOCAL_ BILLED_IN_ Obs ID TO CURRENCY COUNTRY US_DOLLARS 1 11270 39045213 8738600640.00 Brazil 3675848666.21 2 11271 18543489 11063836.00 USA 11063836.00 3 11273 19783482 252148.50 USA 252148.50 4 11276 14324742 1934460.00 USA 1934460.00 5 11278 14898029 1400825.00 USA 1400825.00 6 11280 39045213 8738600640.00 Brazil 3675848666.21 7 11282 19783482 252148.50 USA 252148.50 8 11285 38763919 2234301.30 Argentina 772847.05 * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; libname mdb "C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb"; data invoice; set mdb.invoice; run;quit; ... View more

Post a sample workbook and I will handle slightly misspelled names(as long as the misspelling is minor) also keep just the columns you want. Even easier if the positions of the columns are the same in each sheet. Ie A always has name or names(slight mispelling. ... View more

Importing all tabs/sheets from single excel file into one single SAS dataset If you do not have named ranges, which is the default when you create a sheet with the libname engine, you can use "&sheet"n where sheet =class0$ https://goo.gl/57cALI https://communities.sas.com/t5/SAS-Procedures/Importing-all-tabs-sheets-from-single-excel-file-into-one-single/m-p/347878 HAVE a excel workbook with three sheets (three splits od sashelp.class) d:/xls/tree.xlsx CLASS0 sheet +-------------------------+------+-----------+------------ | | A | B | C | D | E | +------+-----------+------+------+-----------+-----------+ | | | | | | | | 1 | NAME | AGE| SEX | HEIGHT | WEIGHT | | 2 | Alfred | 14 | M | 55 | 96 | | 3 | Alice | 13 | F | 44 | 87 | | ... | ... | ... | ... | .. | ... | +------------------+------+------+-----------+-----------+ [CLASS0] .... CLASS2 sheet +-------------------------+------+-----------+------------ | | A | B | C | D | E | +------+-----------+------+------+-----------+-----------+ | | | | | | | | 1 | NAME | AGE| SEX | HEIGHT | WEIGHT | | 2 | Ronald | 14 | M | 55 | 96 | | 3 | William | 13 | F | 44 | 87 | | ... | ... | ... | ... | .. | ... | +------------------+------+------+-----------+-----------+ [CLASS2] WANT (one SAS dataset) ==== Up to 40 obs from allsheets total obs=19 Obs NAME SEX AGE HEIGHT WEIGHT 1 Barbara F 13 65.3 98.0 2 James M 12 57.3 83.0 3 Jeffrey M 13 62.5 84.0 4 Judy F 14 64.3 90.0 5 Philip M 16 72.0 150.0 6 Thomas M 11 57.5 85.0 7 Alfred M 14 69.0 112.5 8 Carol F 14 62.8 102.5 9 Jane F 12 59.8 84.5 10 John M 12 59.0 99.5 11 Louise F 12 56.3 77.0 12 Robert M 12 64.8 128.0 13 William M 15 66.5 112.0 14 Alice F 13 56.5 84.0 15 Henry M 14 63.5 102.5 16 Janet F 15 62.5 112.5 17 Joyce F 11 51.3 50.5 18 Mary F 15 66.5 112.0 19 Ronald M 15 67.0 133.0 * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; * create a excel workbook with three sheets * just in case you rerun; %symdel sheets sheet / nowarn; proc datasets library=work kill; run;quit; %utlfkil(d:/xls/three.xlsx); libname xel "d:/xls/three.xlsx"; data xel.class0 xel.class1 xel.class2; set sashelp.class; select (mod(_n_,3)); when (0) output xel.class0; when (1) output xel.class1; when (2) output xel.class2; *leave off otherise to force error in not inclusive; end; run;quit; libname xel clear; /* NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set XEL.class0 has 6 observations and 5 variables. NOTE: The data set XEL.class1 has 7 observations and 5 variables. NOTE: The data set XEL.class2 has 6 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.03 seconds */ * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; libname xel "d:/xls/three.xlsx"; data _null_; if _n_=0 then do; rc=%sysfunc(dosubl(' proc sql; select quote(trim(memname)) into :sheets separated by "," from sashelp.vtable where libname="XEL" and index(memname,"$")=0 ;quit; ')); end; x=resolve('&sheets'); put x=; length sheet $32; do sheet=&sheets.; call symputx('sheet',sheet); rc=dosubl(' /* handles length issues better than proc append */ data allsheets; set xel.&sheet; run;quit; '); end; run;quit; SYMBOLGEN: Macro variable SHEETS resolves to "class0","class1","class2" X="class0","class1","class2" SYMBOLGEN: Macro variable SHEET resolves to class0 NOTE: There were 7 observations read from the data set XEL.class0. NOTE: The data set WORK.ALLSHEETS has 7 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 3663.53k OS Memory 17644.00k Timestamp 04/06/2017 03:42:39 PM Step Count 177 Switch Count 0 SYMBOLGEN: Macro variable SHEET resolves to class1 NOTE: There were 6 observations read from the data set XEL.class1. NOTE: The data set WORK.ALLSHEETS has 6 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 3663.53k OS Memory 17644.00k Timestamp 04/06/2017 03:42:39 PM Step Count 177 Switch Count 0 SYMBOLGEN: Macro variable SHEET resolves to class2 NOTE: There were 6 observations read from the data set XEL.class2. NOTE: The data set WORK.ALLSHEETS has 6 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 3663.53k OS Memory 17644.00k Timestamp 04/06/2017 03:42:39 PM Step Count 177 Switch Count 0 ... View more

WPS/SAS: DOSUBL solution for splitting a SAS dataset WPS does not support DOSUBL SOAPBOX ON You may want to use this in non-production jobs. If many of us use it, there is less of a chance SAS and/or Corporate IT will discourage, weaken or remove this powerful 'subroutine' function in 9.4M4. We do not want this to go the route of the store command, window, %window, pmenu, command macros, libanme, passthru to excel/access , command line, function keys... Note you almost never see SAS supporting the lost functionality above but you see SAS often supporting SAS studio and EG. It would help if SAS will unequivocally state that it will support and enhance all current DOSUBL functionality on all interfaces (editors not just EG and SAS Studio) and all platforms. I don't understand why SAS employees are not promoting DOSUBL by using it to respond to posts like this one. I have experienced push back. SOAPBOX OFF Unlike the HASH this does not require a sort. Unlike 'call execute' and macro solutions we can stop the generation of code as soon as a 'user defined' or system warning/error occurs. This post https://goo.gl/aHXBoZ https://communities.sas.com/t5/Base-SAS-Programming/Splitting-up-dataset-based-on-unique-variable/m-p/347689 HAVE ==== Up to 40 obs WORK.HAVE total obs=9 Obs ID VAR1 VAR2 1 one 100 200 2 one 100 200 3 three 300 400 4 three 500 200 5 three 200 100 6 two 400 200 7 two 300 166 8 one 300 100 9 three 400 400 WANT ==== WORK.ONE has 3 obs WORK.TWO has 2 obs WORK.THREE has 4 obs Up to 40 obs from WORK.ONE total obs=3 Obs ID VAR1 VAR2 1 one 300 100 2 one 100 200 3 one 100 200 Up to 40 obs from WORK.TWO total obs=2 Obs ID VAR1 VAR2 1 two 400 200 2 two 300 166 Up to 40 obs from WORK.THREE total obs=4 Obs ID VAR1 VAR2 1 three 500 200 2 three 200 100 3 three 300 400 4 three 400 400 WORKING CODE ============ WPS/SAS declare hash h(dataset:'have(obs=0)',multidata:'y', ordered: "A"); h.output(dataset:id); * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; data have; input ID $ var1 var2; datalines; one 100 200 three 300 400 three 500 200 three 200 100 two 400 200 two 300 166 one 300 100 three 400 400 ;;;; run;quit; %symdel dsn sqlobs id / nowarn; data _null_; If _n_=0 then do; rc=%sysfunc(dosubl(' proc sql; select distinct quote(trim(id)) into :id separated by "," from have;quit; ')); end; length id $5; do id=&id; call symputx('dsn',id); rc=dosubl(' proc sql; create table &dsn as select * from have where id="&dsn" ;quit; '); if &sqlobs=0 then put "dataset " id " failed with 0 observations stoppin code generation"; end; run;quit; NOTE: Table WORK.ONE created, with 2 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: Table WORK.THREE created, with 4 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: Table WORK.TWO created, with 2 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 2.21 seconds cpu time 0.28 seconds ... View more

Create array of price change combinations This is best done in IML or R. In order to print the max on the first record I had to load a two dimesional array and find the max of all elements and then load load and output a seven element array redirect to this message https://goo.gl/6gxqsO https://communities.sas.com/t5/Base-SAS-Programming/Create-array-of-price-change-combinations/m-p/347274 HAVE ==== Up to 40 obs WORK.HAVE total obs=7 Obs DAY PRICE 1 1 100.0 2 2 101.5 3 3 103.0 4 4 104.5 5 5 106.0 6 6 107.5 7 7 109.0 WANT ==== DETAILS DAY1 PCT2 = 100*(Price[2]/Price[1] - 1) ... PCT7=100*(Price[7]/Price[1] - 1) .. DAY6 PCT7 = 100*(Price[7]/Price[6] - 1) 100*(109/107.5 - 1) = 1.3953488372 * ___ __ _ ___ / __|/ _` / __| \__ \ (_| \__ \ |___/\__,_|___/ ; DAY BASE PCT1 PCT2 PCT3 PCT4 PCT5 PCT6 PCT7 MAX 1.0 100.0 . 1.5 3.0 4.5 6.0 7.5 9.0 9.0 2.0 101.5 . . 1.5 3.0 4.4 5.9 7.4 . 3.0 103.0 . . . 1.5 2.9 4.4 5.8 . 4.0 104.5 . . . . 1.4 2.9 4.3 . 5.0 106.0 . . . . . 1.4 2.8 . 6.0 107.5 . . . . . . 1.4 . PCT7 =100*(109/107.5 - 1) = 1.3953488372 * ____ __ ___ __ ___ | _ \ \ \ /\ / / '_ \/ __|_____| |_) | \ V V /| |_) \__ \_____| _ < \_/\_/ | .__/|___/ |_| \_\ |_| ; 40 obs WORK.WANT total obs=6 Obs DAY PRICE PCT2 PCT3 PCT4 PCT5 PCT6 PCT7 MAX 1 1.0 100.0 1.5 3.0 4.5 6.0 7.5 9.0 9.0 2 2.0 101.5 . 1.5 3.0 4.4 5.9 7.4 . 3 3.0 103.0 . . 1.5 2.9 4.4 5.8 . 4 4.0 104.5 . . . 1.4 2.9 4.3 . 5 5.0 106.0 . . . . 1.4 2.8 . 6 6.0 107.5 . . . . . 1.4 . FULL SOLUTION ============= * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; options validvarname=upcase; libname sd1 "d:/sd1"; data sd1.have; retain day price; input Price; day=_n_; cards4; 100 101.5 103 104.5 106 107.5 109 ;;;; run;quit; *_ ______ ____ ____ \ \ / / _ \/ ___| | _ \ \ \ /\ / /| |_) \___ \ _____| |_) | \ V V / | __/ ___) |_____| _ < \_/\_/ |_| |____/ |_| \_\ ; %utl_submit_wps64(' libname sd1 "d:/sd1"; options set=R_HOME "C:/Program Files/R/R-3.3.2"; libname wrk "%sysfunc(pathname(work))"; libname hlp "C:\Program Files\SASHome\SASFoundation\9.4\core\sashelp"; proc r; submit; source("C:/Program Files/R/R-3.3.2/etc/Rprofile.site", echo=T); library(haven); have<-t(read_sas("d:/sd1/have.sas7bdat")[,2]); mat=have[rep(seq_len(nrow(have)), each=6),]; bas<-cbind(have[1:6],mat); fin<-100*(bas[,2:8]/bas[,1] - 1); fin[lower.tri(fin)] <- NA; fin[ row(fin) == col(fin) ] <- NA; fin[1,1]<-max(fin,na.rm = TRUE); endsubmit; import r=fin data=wrk.fin; run;quit; '); data want; retain day price pct2-pct7 max; merge sd1.have(obs=6) fin(rename=(v2-v7=pct2-pct7 v1=max)); run;quit; proc print data=want width=min; format _numeric_ 8.1; run;quit; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %symdel obs /nowarn; libname sd1 "d:/sd1"; data want; retain day base maxfin; if _n_=0 then do; rc=%sysfunc(dosubl('proc sql noprint;select count(*) into :obs separated by "" from sd1.have;quit;')); end; do rec=1 to &obs; array pry[&obs] jan1-jan&obs; array pct[&obs] pct1-pct&obs; array fin[&obs,&obs] fin1-fin%eval(%eval(&obs * &obs)); set sd1.have; pry[rec]=price; end; do day=1 to &obs - 1; do mat=day+1 to &obs; fin[day,mat]=100*pry[mat]/pry[day] -100; base=pry[day]; end; end; maxfin=max(of fin[*]); put maxfin=; do day=1 to &obs - 1; do mat=1 to &obs; fins+1; pct[mat]=fin[day,mat]; end; if day ne 1 then maxfin=.; output; end; keep day base pct: maxfin; run;quit; proc print data=want width=min; format _numeric_ 8.1; run;quit; ... View more

When to use 'input; _infile_' combinations formatting not working this is a retry see https://goo.gl/pv00gD https://communities.sas.com/t5/forums/replypage/board-id/programming/message-id/80251 HAVE (missing data, oddly spaced data and unknown variable lengths in a SAS interactive old text editor) ========================================================================================================= Because editors like EE, EG, SAS Studio are inconsistent with inline data, you may want stick with just one. Line lengths, how blank lines are processed and '.' may vary. I like to stick with the 'old text editor', which was rock solid prior to 1984? HAVE ==== cards4; D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 WANT ==== Up to 40 obs WORK.F3_FINAL_LIST total obs=8 Obs NAME VALUE 1 D_VR_comgap_stb_lb6_ord3_d2_x1 100 2 D_W_VR_comgap_lb10_stb_cd_xac123 2 3 D_std90_gt_90 0 4 Q . 5 S . 6 QD_VRcomgap_stb_lb10_ord3_d1_x . 7 DW_VRcomgap_stb_lb6_ord3_d1_y . 8 D_sys2_cnt_trend_prior 0 WORKING CODE ============ input; name=scan(_infile_,1,' '); value=input(scan(_infile_,2,' '),4.); FULL SOLUTION ============= data F3_final_list; length name $384 ; * longest editor line in old text editor; input; name=scan(_infile_,1,' '); value=input(scan(_infile_,2,' '),4.); cards4; D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 ;;;; run;quit; 2735 data F3_final_list; 2736 length name $384 ; * longest editor line in old text editor; 2737 input; 2738 name=scan(_infile_,1,' '); 2739 value=input(scan(_infile_,2,' '),4.); 2740 cards4; NOTE: The data set WORK.F3_FINAL_LIST has 8 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds In the old text editor If you save this in your autocall library and turn on the command macros, 'options cmdmac;' Hight light the cards data and type 'cuth' on the clean old text editor command line %macro cuth/cmd; %do i=1 %to 20; c ' ' ' ' all; %end; %mend cuth; You can also use a function key ie 'F1 cuth' HIGHLIGHT THIS D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 YOU GET THIS D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 2734 run;quit; MLOGIC(CUTH): Beginning execution. MLOGIC(CUTH): This macro was compiled from the autocall file c:\oto\cuth.sas MLOGIC(CUTH): %DO loop beginning; index variable I; start value is 1; stop value is 20; by value is 1. MLOGIC(CUTH): %DO loop index variable I is now 2; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 3; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 4; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 5; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 6; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 7; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 8; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 9; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 10; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 11; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 12; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 13; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 14; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 15; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 16; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 17; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 18; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 19; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 20; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 21; loop will not iterate again. MLOGIC(CUTH): Ending execution. ... View more

When to use 'input; _infile_' combinations this post https://goo.gl/pv00gD https://communities.sas.com/t5/forums/replypage/board-id/programming/message-id/80251 HAVE (missing data, oddly spaced data and unknown variable lengths in a SAS interactive old text editor) ========================================================================================================= Because editors like EE, EG, SAS Studio are inconsistent with inline data, you may want stick with just one. Line lengths, how blank lines are processed and '.' vary from editor to editor. I like to stick with the 'old text editor', which was rock solid prior to 1984, but is deteriorating HAVE ==== cards4; D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 WANT ==== Up to 40 obs WORK.F3_FINAL_LIST total obs=8 Obs NAME VALUE 1 D_VR_comgap_stb_lb6_ord3_d2_x1 100 2 D_W_VR_comgap_lb10_stb_cd_xac123 2 3 D_std90_gt_90 0 4 Q . 5 S . 6 QD_VRcomgap_stb_lb10_ord3_d1_x . 7 DW_VRcomgap_stb_lb6_ord3_d1_y . 8 D_sys2_cnt_trend_prior 0 WORKING CODE ============ input; name=scan(_infile_,1,' '); value=input(scan(_infile_,2,' '),4.); FULL SOLUTION ============= data F3_final_list; length name $384 ; * longest editor line in old text editor; input; name=scan(_infile_,1,' '); value=input(scan(_infile_,2,' '),4.); cards4; D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 ;;;; run;quit; 2735 data F3_final_list; 2736 length name $384 ; * longest editor line in old text editor; 2737 input; 2738 name=scan(_infile_,1,' '); 2739 value=input(scan(_infile_,2,' '),4.); 2740 cards4; NOTE: The data set WORK.F3_FINAL_LIST has 8 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds In the old text editor If you save this in your autocall library and turn on the command macros, 'options cmdmac;' Hight light the cards data and type 'cuth' on the clean old text editor command line %macro cuth/cmd; %do i=1 %to 20; c ' ' ' ' all; %end; %mend cuth; You can also use a function key ie 'F1 cuth' HIGHLIGHT THIS D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 YOU GET THIS D_VR_comgap_stb_lb6_ord3_d2_x1 100 D_W_VR_comgap_lb10_stb_cd_xac123 2 D_std90_gt_90 0 Q . S . QD_VRcomgap_stb_lb10_ord3_d1_x DW_VRcomgap_stb_lb6_ord3_d1_y D_sys2_cnt_trend_prior 0 2734 run;quit; MLOGIC(CUTH): Beginning execution. MLOGIC(CUTH): This macro was compiled from the autocall file c:\oto\cuth.sas MLOGIC(CUTH): %DO loop beginning; index variable I; start value is 1; stop value is 20; by value is 1. MLOGIC(CUTH): %DO loop index variable I is now 2; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 3; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 4; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 5; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 6; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 7; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 8; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 9; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 10; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 11; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 12; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 13; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 14; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 15; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 16; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 17; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 18; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 19; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 20; loop will iterate again. MLOGIC(CUTH): %DO loop index variable I is now 21; loop will not iterate again. MLOGIC(CUTH): Ending execution. ... View more

May I suggest setting meta data. data meta; input dsn : $18. start time8. end time8.; cards4; sashelp.class 00:00:00 18:00:00 sashelp.iris 18:00:00 24:00:00 sashelp.qrtns 18:00:00 24:00:00 ;;;; run;quit; * Unlike 'call execute and many macro solutions you can set whst ever criteria you want to stop the execution of the iteration of a macro or 'call execute'; data _null_; set meta; put dsn=; call symputx('symdsn',dsn); do tym=start to end by 180*60; rc=dosubl(' proc sql; create table tmpdat as select * from &symdsn ;quit; data %scan(&symdsn,1,%str(.)); retain seq 0; set tmpdat; seq=_n_; run;quit; %let Errortext= &syserrortext; %let Errorcode= &syserr; * you can set your own logic for failure; * like 0 obs; '); if symget('errorcode') ne "0" then do; errortext=symget('errortext'); put "dataset creation failed for " dsn " stopping parent"; put "Error message " errortext; stop; end; end; run;quit; ... View more

My mistake. I think this may be what you want. data have(where=(num7 ne 0)); retain seq cid; retain num1-num7 0; array therest num1-num7; do dig=1000000 to 2000000 by 5731; seq=substr(put(dig,7.),5); cid=substr(put(dig,7.),4); if uniform(5731)<.33 then do; do over therest; therest=int(1000*uniform(5764)); end; output; substr(cid,1,1)='X'; substr(seq,1,1)='X'; output; if uniform(6754)<.66 then do; substr(cid,1,1)='Z'; substr(seq,1,1)='Z'; output; end; end; else do; do over therest; therest=int(1000*uniform(5764)); end; output; end; end; drop dig; run;quit; * SOLUTION; data want; set have; by num1--num7 notsorted; * note in your case record_num--cost; if first.num7 and last.num7; run;quit; Up to 40 obs WORK.WANT total obs=114 Obs SEQ CID NUM1 NUM2 NUM3 NUM4 NUM5 NUM6 NUM7 1 731 5731 514 301 802 34 703 218 338 2 462 1462 839 114 283 549 73 89 748 3 193 7193 457 379 774 663 240 344 785 4 924 2924 343 384 64 491 362 314 361 5 117 0117 772 826 164 560 317 986 897 6 848 5848 856 911 99 742 75 125 528 7 310 7310 599 37 836 77 101 829 245 8 041 3041 395 33 557 885 650 990 343 9 503 4503 744 490 117 202 182 869 767 10 965 5965 132 17 475 692 988 401 334 ... View more

Importing entire directory of XLS file into SAS This requires full SAS so if yo get a error you may want to contact your IT dept and show them the error. This does not solve a CSV problem because it deals with a directory of excel files. I tried to use 'proc import' but it crashed SAS. DM commands were ignored. You have much more flexibilty with excel files. HAVE ==== Directory of d:\xls cars.xlsx 04/04/2017 04:53 PM 49,376 class.xlsx 04/04/2017 04:53 PM 9,253 iris.xlsx 04/04/2017 04:53 PM 12,870 WANT ==== WORK.CARS WORK.CLASS WORK.IRIS WORKING CODE ============ rc=filename("mydir","d:\xls"); did=dopen("mydir"); rc=dosubl(' libname xel "d:/xls/&mem..xlsx"; data &mem; set xel.&mem; * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; CREATE A DIRECTORY OF EXCEL FILES ================================= dm "dexport sashelp.class 'd:\xls\class.xlsx' replace"; dm "dexport sashelp.cars 'd:\xls\cars.xlsx' replace"; dm "dexport sashelp.iris 'd:\xls\iris.xlsx' replace"; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %symdel mem / nowarn; data _null_; rc=filename("mydir","d:\xls"); did=dopen("mydir"); if did > 0 then do; memcount=dnum(did); do i=1 to memcount; name=dread(did,i); call symputx('mem',scan(name,1,'.')); put name=; rc=dosubl(' libname xel "d:/xls/&mem..xlsx"; data &mem; set xel.&mem; run;quit; libname xel clear; '); end; end; stop; run;quit; NAME=cars.xlsx NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/cars.xlsx NOTE: There were 428 observations read from the data set XEL.cars. NOTE: The data set WORK.CARS has 428 observations and 15 variables. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.07 seconds NOTE: Libref XEL has been deassigned. NAME=class.xlsx NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/class.xlsx NOTE: There were 19 observations read from the data set XEL.class. NOTE: The data set WORK.CLASS has 19 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: Libref XEL has been deassigned. NAME=iris.xlsx NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/iris.xlsx NOTE: There were 150 observations read from the data set XEL.iris. NOTE: The data set WORK.IRIS has 150 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds NOTE: Libref XEL has been deassigned. NOTE: DATA statement used (Total process time): real time 0.55 seconds cpu time 0.35 seconds 48 ! quit; ... View more