Can you post   Proc options group=memory; run;quit;   I just saw 16gb of DDR3 ram dor $79, hopefully you have at least 16gb. ... View more

For the current documentation for the R package SASxport see   https://cran.r-project.org/web/packages/SASxport/SASxport.pdf   For bugs see https://github.com/MangoTheCat/SASxport/issues   There are two outstanding issues.    Note R, Python and Perl can read sas7bdats   WPS and SAS can create SAS datasets from R dataframes. The express verison of WPS does not limit the size of SAS datasets created from R.   Evan Miller and Hadley Wickham have an experimnetal R package that can create SAS datasets. However it has acknowledged bugs which are documented and under development. It is not usable yet.   I think Matt Shotwell is working on a Java API to SAS datasets?   I have used StatTransfer and it works well. There is also Carolina software, which uses JDBC? I have not used it.   ... View more

SAS conversion tool You should be able to cut and paste this code into the SAS IML/R interface inspired by https://goo.gl/RGB2KK https://communities.sas.com/t5/Base-SAS-Programming/SAS-conversion-tool/m-p/330292 Here is a pure R solution. Should also be possible in Python or Perl. WORKS BEST WITH THE 1980 SAS Old text editor HAVE d:/csv/class.csv ====================== NAME,SEX,AGE,HEIGHT,WEIGHT Alfred,M,14,69,112.5 Alice,F,13,56.5,84 Barbara,F,13,65.3,98 Carol,F,14,62.8,102.5 Henry,M,14,63.5,102.5 James,M,12,57.3,83 Jane,F,12,59.8,84.5 Janet,F,15,62.5,112.5 Jeffrey,M,13,62.5,84 John,M,12,59,99.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 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 WANT d:/xpt/class.xpt (V5 export format) ==== WORKING CODE ============ R class <-import("d:/csv/class.csv"); write.xport( class, file = "d:/xpt/class.xpt" ); FULL SOLUTION ============= * create comma separated file; dm "dexport sashelp.class 'd:/csv/class.csv' replace"; %utl_submit_r64(' source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T); library(SASxport); library(rio); class <-import("d:/csv/class.csv"); write.xport( class, file = "d:/xpt/class.xpt" ); '); libname xpt xport "d:/xpt/class.xpt"; data classxpt; set xpt.class; run;quit; proc print data=classxpt width=min; title "from SAS proc print"; run;quit; * check the xpt file; 247 libname xpt xport "d:/xpt/class.xpt"; NOTE: Libref XPT was successfully assigned as follows: Engine: XPORT Physical Name: d:\xpt\class.xpt 248 data classxpt; 249 set xpt.class; 250 run; NOTE: There were 19 observations read from the data set XPT.CLASS. NOTE: The data set WORK.CLASSXPT has 19 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 250 ! quit; 251 proc print data=classxpt width=min; 252 title "from SAS proc print"; 253 run; NOTE: There were 19 observations read from the data set WORK.CLASSXPT. NOTE: PROCEDURE PRINT used (Total process time): real time 0.06 seconds cpu time 0.04 seconds 253 ! quit; ... View more

I ran with your data and included the log. You need to sort have by country. The code below worked with your data. You may get better responses from SAS-L. I cross post interesting posts. see https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;22c6ba79.1702a and https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;eac1033b.1702a see also * delete the excel files if they exist; * you can do this by hand; %utlfkil(d:/xls/CANADA.xlsx); %utlfkil(d:/xls/GERMANY.xlsx); %utlfkil(d:/xls/USA.xlsx); DATA Have; INPUT Country $ Country_Code Region $; DATALINES; Japan 107 Asia USA 109 America France 103 Europe Canada 111 America India 105 Asia Nepal 115 Asia Italy 102 Europe ; RUN; proc sort data=have out=have; by country; run;quit; %symdel country; data _null_; set have; by country; if first.country then do; call symputx('country',compress(country,' .'),'g'); rc=dosubl(' libname xel "d:/xls/&country..xlsx"; data xel.&country; set have(where=(country="&country")); run;quit; libname xel clear; run;quit; '); end; run;quit; 1517 DATA Have; 1518 INPUT Country $ Country_Code Region $; 1519 DATALINES; NOTE: The data set WORK.HAVE has 7 observations and 3 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 359.15k OS Memory 19948.00k Timestamp 02/05/2017 10:31:36 AM Step Count 141 Switch Count 0 1527 ; 1528 RUN; 1529 proc sort data=have out=have; 1530 by country; 1531 run; NOTE: There were 7 observations read from the data set WORK.HAVE. NOTE: The data set WORK.HAVE has 7 observations and 3 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 570.84k OS Memory 19948.00k Timestamp 02/05/2017 10:31:36 AM Step Count 142 Switch Count 0 1531! quit; 1532 %symdel country; 1533 data _null_; 1534 set have; 1535 by country; 1536 if first.country then do; 1537 call symputx('country',compress(country,' .'),'g'); 1538 rc=dosubl(' 1539 libname xel "d:/xls/&country..xlsx"; 1540 data xel.&country; 1541 set have(where=(country="&country")); 1542 run;quit; 1543 libname xel clear; 1544 run;quit; 1545 '); 1546 end; 1547 run; SYMBOLGEN: Macro variable COUNTRY resolves to Canada NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/Canada.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to Canada SYMBOLGEN: Macro variable COUNTRY resolves to Canada NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='Canada'; NOTE: The data set XEL.Canada has 1 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 1085.43k OS Memory 19948.00k Timestamp 02/05/2017 10:31:41 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. SYMBOLGEN: Macro variable COUNTRY resolves to France NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/France.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to France SYMBOLGEN: Macro variable COUNTRY resolves to France NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='France'; NOTE: The data set XEL.France has 1 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:41 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. SYMBOLGEN: Macro variable COUNTRY resolves to India NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/India.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to India SYMBOLGEN: Macro variable COUNTRY resolves to India NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='India'; NOTE: The data set XEL.India has 1 observations and 3 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 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:42 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. SYMBOLGEN: Macro variable COUNTRY resolves to Italy NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/Italy.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to Italy SYMBOLGEN: Macro variable COUNTRY resolves to Italy NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='Italy'; NOTE: The data set XEL.Italy has 1 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:42 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. SYMBOLGEN: Macro variable COUNTRY resolves to Japan NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/Japan.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to Japan SYMBOLGEN: Macro variable COUNTRY resolves to Japan NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='Japan'; NOTE: The data set XEL.Japan has 1 observations and 3 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 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:42 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. SYMBOLGEN: Macro variable COUNTRY resolves to Nepal NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/Nepal.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to Nepal SYMBOLGEN: Macro variable COUNTRY resolves to Nepal NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='Nepal'; NOTE: The data set XEL.Nepal has 1 observations and 3 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 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:42 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. SYMBOLGEN: Macro variable COUNTRY resolves to USA NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/USA.xlsx SYMBOLGEN: Macro variable COUNTRY resolves to USA SYMBOLGEN: Macro variable COUNTRY resolves to USA NOTE: There were 1 observations read from the data set WORK.HAVE. WHERE country='USA'; NOTE: The data set XEL.USA has 1 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:43 AM Step Count 143 Switch Count 0 NOTE: Libref XEL has been deassigned. NOTE: There were 7 observations read from the data set WORK.HAVE. NOTE: DATA statement used (Total process time): real time 6.73 seconds user cpu time 0.24 seconds system cpu time 0.35 seconds memory 1085.68k OS Memory 19948.00k Timestamp 02/05/2017 10:31:43 AM Step Count 143 Switch Count 7 1547! quit; ... View more

HAVE === Up to 40 obs WORK.HAVE total obs=15 Obs COUNTRY REC 1 CANADA 1 2 CANADA 2 3 CANADA 3 4 CANADA 4 5 CANADA 5 6 GERMANY 1 7 GERMANY 2 8 GERMANY 3 9 GERMANY 4 10 GERMANY 5 11 USA 1 12 USA 2 13 USA 3 14 USA 4 15 USA 5 WANT d:/xls/CANADA.xlsx d:/xls/GERMANY.xlsx d:/xls/USA.xlsx SHEET CANADA IN d:/xls/CANADA.xlsx +-------------------------+ | A | B | +-------------------------+ 1 | NAME | REC | +------------+------------+ 2 | CANADA | 1 | +------------+------------+ ... +------------+------------+ 5 | CANADAM | 5 | +------------+------------+ [CANADA] ... SHEET CANADA IN d:/xls/USA.xlsx +-------------------------+ | A | B | +-------------------------+ 1 | NAME | REC | +------------+------------+ 2 | USA | 1 | +------------+------------+ ... +------------+------------+ 5 | USA | 5 | +------------+------------+ [CANADA] ... WORKING CODE rc=dosubl(' libname xel "d:/xls/&country..xlsx"; data xel.&country; set have(where=(country="&country")); run;quit; libname xel clear; run;quit; FULL SOLUTION ============ *create some data; data have; do country="CANADA ","GERMANY","USA"; do rec=1 to 5; output; end; end; run;quit; %symdel country; *just in case you have this variable in global scope; data _null_; set have; by country; if first.country then do; call symputx('country',compress(country,' .'),'g'); rc=dosubl(' libname xel "d:/xls/&country..xlsx"; data xel.&country; set have(where=(country="&country")); run;quit; libname xel clear; run;quit; '); end; run;quit; ... View more

Annual Percentage Change with 95%CIs inspired by https://goo.gl/VPn5mo https://communities.sas.com/t5/SAS-Procedures/Annual-Percentage-Change-with-95-CIs/m-p/329864 * Three techniques 1. proc freq exact (hypergeomethic?) 6.462% 6.478% 2. Normal approximation 6.461 6.479 3. Exact using an interative datastep solution 0.064622 0.064784 HAVE ( I restructure your data ) Up to 40 obs WORK.HAVE total obs=14 Obs YER EVENT TOT 1 2004 0 2313308 Have HIV 2 2004 1 33439457 Do not have AIDS 3 2005 0 2468735 4 2005 1 33516847 5 2006 0 2043291 6 2006 1 34203531 7 2007 0 1848984 8 2007 1 34703545 9 2008 0 1684509 10 2008 1 35171713 11 2009 0 1704744 12 2009 1 35372460 13 2010 0 1424794 14 2010 1 35893687 WANT lower upper Obs YER XL_BIN XU_BIN Normal Approx see below Exact hand calculated 1 2004 6.462% 6.478% ( 6.461, 6.479 ) 0.064622 0.064784 2 2005 6.852% 6.869% 3 2006 5.630% 5.645% 4 2007 5.051% 5.066% 5 2008 4.564% 4.577% 6 2009 4.591% 4.605% 7 2010 3.812% 3.824% SOLUTION ======== * I had to do some reformating; data have; input Yer pop pct lwr upr; inc=int(pop*(lwr+upr)/200); lwrupr=(lwr+upr)/2; put lwrupr; event=0;tot=inc;output; event=1;tot=pop-inc;output; keep yer event tot; cards4; 2004 35752765 246 6.8805867 6.06 7.78 2005 35985582 262 7.2806937 6.44 8.2 2006 36246822 218 6.0143204 5.26 6.85 2007 36552529 198 5.4168619 4.7 6.21 2008 36856222 181 4.9109754 4.23 5.67 2009 37077204 183 4.9356473 4.26 5.69 2010 37318481 148 3.9658635 3.67 4.64 ;;;; run;quit; proc freq data=have; by yer; tables event / list bin exact alpha=.05 out=cnt; weight tot; output out=rsk bin ; run;quit; proc print data=rsk; format xl_bin xu_bin percent8.5; var yer xl_bin xu_bin; run;quit; %macro utl_exbnci(n=,x=,p1=,p2=,alpha=); %global &p1 &p2; data _null_; p1low=0.0; p1mid=0.5; p1high=1.0; p2low=0.0; p2mid=0.5; p2high=1.0; n=&n; x=&x; pval=&alpha/2; if x=0 then p1mid=0; else do until(p1high-p1low < 0.0001); if 1-probbnml(p1mid,n,x-1) < pval then p1low=p1mid; else p1high=p1mid; p1mid=(p1low+p1high)/2; end; do until(p2high-p2low < 0.0001); if probbnml(p2mid,n,x) < pval then p2high=p2mid; else p2low=p2mid; p2mid=(p2low+p2high)/2; end; call symput("&p1",put(100*p1mid,5.3)); call symput("&p2",put(100*p2mid,5.3)); run; %mend utl_exbnci; *----------* | examples | *----------*; %utl_exbnci(n=35752765,x=2313308,p1=plow,p2=phigh,alpha=0.05) data _null_; put @5 "ci - ( &plow, &phigh )"; run; /* T000101 DATASTEP INTERACTIVE METHOD FOR BINOMIOAL CONFIDENCE INTERVAL WORKS WITH 0 RESPONDERS */ %macro utl_bnmlcia(indata=, /* input data set name */ outdata=bnmlci /* output data set name */ ); data &outdata; format n m best12. alpha p 8.6 l_limit u_limit 8.6 err_msg $55.; set &indata; if n=int(n) and m=int(m) and n>0 and m>=0 and m<=n and 0<alpha<1 then do; p=m/n; alpha2=alpha/2; if m=0 then do; l_limit=0; link FIND_UL; end; else if m=n then do; u_limit=1; link FIND_LL; end; else do; link FIND_LL; link FIND_UL; end; end; else do; put n= m= alpha=; if n ne int(n) or n<=0 then do; err_msg='Variable n needs to be a positiv integer.'; put 'ERROR: ' err_msg; end; if m ne int(m) or m<0 then do; err_msg='Variable m needs to be a nonnegative integer.'; put 'ERROR: ' err_msg; end; if m>n then do; err_msg='Variable n needs to be equal or larger than variable m.'; put 'ERROR: ' err_msg; end; if not (0<alpha<1) then do; err_msg='Variable alpha needs to be between 0 and 1.'; put 'ERROR: ' err_msg; end; put; end; RETURN; FIND_LL: /* Bisection search for lower limit */ xu=p; xl=0; do until (crit < 0.000000001); xm=(xu+xl)/2; ptemp=probbnml(xm, n, m-1); crit=abs(alpha2-(1-ptemp)); if ptemp<(1-alpha2) then do; xl=xl; xu=xm; end; else do; xl=xm; xu=xu; end; end; l_limit=xm; RETURN; FIND_UL: /* Bisection search for upper limit */ xu=1; xl=p; do until (crit < 0.000000001); xm=(xu+xl)/2; ptemp=probbnml(xm, n, m); crit=abs(alpha2-ptemp); if ptemp<alpha2 then xu=xm; else xl=xm; end; u_limit=xm; RETURN; label n = 'n' m = 'm' p = 'm/n' alpha = 'Alpha Value' l_limit = 'CI Lower Limit' u_limit = 'CI Upper Limit'; drop alpha2 xu xl crit xm ptemp err_msg; run; %mend utl_bnmlcia; ****************** An Example **********************; data t; input n m alpha; cards; 35752765 2313308 0.05 ; run; %utl_bnmlcia(indata=t, outdata=t1); proc print data=t1; title1 "Binomial Distribution Confidence Intervals"; run; Binomial Distribution Confidence Intervals Obs N M ALPHA P L_LIMIT U_LIMIT 1 35752765 2313308 0.050000 0.064703 0.064622 0.064784 ... View more

Data want; set have(drop=vector:); run;quit;   ... View more

Create four region datasets from one master dataset 1. Hash method Martin, Vincent (STATCAN) via listserv.uga.edu 2. Dosubl method 3 Collection of other solutions; HAVE ==== Up to 40 obs WORK.EMPLOYEE total obs=7 COUNTRY_ Obs NAME COUNTRY_CODE REGION 1 Japan 107 Asia 2 USA 109 America 3 France 103 Europe 4 Canada 111 America 5 India 105 Asia 6 Nepal 115 Asia 7 Italy 102 Europe WANT === Member Obs, Entries File # Name Type or Indexes Vars Label Size Last Modified 1 AMERICA DATA 2 3 131072 02/02/2017 12:43:52 2 ASIA DATA 3 3 131072 02/02/2017 12:43:52 3 EMPLOYEE DATA 7 3 131072 02/02/2017 12:43:52 4 EUROPE DATA 2 3 131072 02/02/2017 12:43:52 WORKING CODE ============ HASH do while(hi.next()=0); h.output(dataset: region||"(where=(region="||quote(region)||"))" ); end; DOSUBL call symputx('region',region); rc=dosubl(' data &region; set employee(where=(region="&region")); run;quit; '); HASH SOLUTION ============= * create some data; DATA Employee; INPUT Country_Name $ Country_Code Region $; cards4; Japan 107 Asia USA 109 America France 103 Europe Canada 111 America India 105 Asia Nepal 115 Asia Italy 102 Europe ;;;; run;quit; Martin, Vincent (STATCAN) via listserv.uga.edu data _null_; if 0 then set employee; declare hash h(dataset: "work.employee"); h.definekey("country_name"); h.definedata(all: "y"); h.definedone(); declare hash outctrl(dataset: "work.employee(keep=region)"); declare hiter hi("outctrl"); outctrl.definekey("region"); outctrl.definedata("region"); outctrl.definedone(); do while(hi.next()=0); h.output(dataset: region||"(where=(region="||quote(region)||"))" ); end; run; DOSUBL SOLUTION =============== proc datasets lib=work kill; run;quit; proc sort data=employee out=have; by region; run;quit; data _null_; set have; by region; if last.region then do; call symputx('region',region); rc=dosubl(' data &region; set employee(where=(region="&region")); run;quit; '); end; run;quit; proc contents data=work._all_; run;quit; Member Obs, Entries File # Name Type or Indexes Vars Label Size Last Modified 1 AMERICA DATA 2 3 131072 02/02/2017 12:43:52 2 ASIA DATA 3 3 131072 02/02/2017 12:43:52 3 EMPLOYEE DATA 7 3 131072 02/02/2017 12:43:52 4 EUROPE DATA 2 3 131072 02/02/2017 12:43:52 /* T007960 SAS and R solutions for Splitting datasets SAS and R solutions for Splitting sashelp.class into two datasets, one with females and one with males. The code below will work for any dataset and splitting variable. LARGE DATASET AND SMALL DATASET SOLUTIONS ARE GIVEN 1. R soluton is one line of code. This splits class into dataframes res$F and res$F where res is a list res<-split(class, list(class$SEX), drop = TRUE); 2. SAS HASH solution. Like R this is an in memory solution good for smaller datasets. First the female records are loaded into memory and then sent out to the F dataset, then the males areloaded and output. 3. This is a solution for large datasets. First we create a macro string containing all the levels. Just male and female for outr example. The we use a datastep to build the following program. Only one line has to changed on the program to do another dataset data F M; set sashelp.class; select (sex); when ('F') output F; when ('M') output M; otherwise; end; run; /* change dataset and sex for other data */ sas=';set sashelp.class; select (sex);'; ********************************* ********************************* * LARGE DATASET SOLUTION * ********************************* *********************************; * put the levels of sex into &sec = "F" "M"; proc sql; select distinct quote(strip(sex)) into :sex separated by ' ' from sashelp.class ;quit; %put &=sex; /* the levels "F" "M"; %put &=sqlobs; /* then number of levels */ data _null_; length sas $200; array arysex[&sqlobs] $32 (&sex); sas='data '; call execute(sas); do i=1 to &sqlobs; sas=arysex[i]; call execute(sas); end; /* change dataset and sex for other data */ sas=';set sashelp.class; select (sex);'; call execute(sas); do i=1 to &sqlobs; sas=catx(' ',cats("when ('",arysex[i],"')"),'output',arysex[i],';'); call execute(sas); put sas; end; sas='otherwise;end;run;quit;'; call execute(sas); run;quit; /* Generated code NOTE: CALL EXECUTE generated line. 1 + data 2 + F 3 + M 4 + ;set sashelp.class; select (sex); 5 + when ('F') output F ; 6 + when ('M') output M ; 7 + otherwise;end;run; *. ********************************* ********************************* * SMALL DATASET HASH SOLUTION * ********************************* *********************************; * for small datasets this should be a very fast way to split a dataset. However the data needs to be indexed or sortted on the split variable; proc sort data=sashelp.class out=class noequals; by sex; run;quit; data _null_; * establist the PDV fr both males and females; if 0 then set class; declare hash h(); * name is the primary key; * if you do not have a primary key you can; * use the method below; h.definekey('name'); h.definedata('name','sex','age','height','weight'); h.definedone(); * note this is loading the records into memory - but can consume memory; do until ( last.sex ) ; set class; by sex ; h.add () ; end ; * output dataset will change with next sex; h.output (dataset: sex) ; run ;quit; ************** ************** * R SOLUTION * ************** ************** data "c:\temp\class.sas7bdat"(outrep=WINDOWS_32 compress=no); set sashelp.class; run;quit; %utl_submit_R (%nrbquote ( library(sas7bdat); class<-read.sas7bdat("c:\\temp\\class.sas7bdat"); /* res is a list of two dataframes */ res<-split(class, list(class$SEX), drop = TRUE); res$F; res$M; /* if you want just the names */ res$F$NAME; res$F[1]; ) ); /* RES$F NAME SEX AGE HEIGHT WEIGHT 2 Alice F 13 56.5 84.0 3 Barbara F 13 65.3 98.0 4 Carol F 14 62.8 102.5 7 Jane F 12 59.8 84.5 8 Janet F 15 62.5 112.5 11 Joyce F 11 51.3 50.5 12 Judy F 14 64.3 90.0 13 Louise F 12 56.3 77.0 14 Mary F 15 66.5 112.0 RES$M NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69.0 112.5 5 Henry M 14 63.5 102.5 6 James M 12 57.3 83.0 9 Jeffrey M 13 62.5 84.0 10 John M 12 59.0 99.5 15 Philip M 16 72.0 150.0 16 Robert M 12 64.8 128.0 17 Ronald M 15 67.0 133.0 18 Thomas M 11 57.5 85.0 19 William M 15 66.5 112.0 */ ... View more

This might help? Hits #77 Using a ring of four generations of a SAS dataset inspired by https://goo.gl/FQJGmj https://communities.sas.com/t5/Base-SAS-Programming/SAS-Automation-Running-a-report-for-JUST-a-few-previous-days/m-p/329245 * documentation; http://analytics.ncsu.edu/sesug/2006/SC18_06.PDF With generation datasets you do not need to change your code with daily dataset refreshes. The ring of datasets rolls off the latest dataset and adds the newest, so you can print yesterdays and todays data using, with relative addressing. proc print data=genum(gennum=-1);run;quit; * yesterdays data load; proc print data=genum;run;quit; * todays dataload You have easy access to yesterdays data HAVE ( Will show you how to create these) ========================================== Gen Member # Name Num Type One variable doc) Also Dataset name 1 GENUM DATA Base DOC='This is Fri Feb 3, 2017' GENUM 2 GENUM -3 DATA DOC=This is Tue Jan 31, 2017 GENUM#001 3 GENUM -2 DATA DOC=This is Wed Feb 1, 2017 GENUM#002 4 GENUM -1 DATA DOC=This is Thu Feb 2, 2017 GENUM#003 proc print data=genum(gennum=-3);run;quit; proc print data=genum(gennum=-2);run;quit; proc print data=genum(gennum=-1);run;quit; proc print data=genum(gennum;run;quit; Tue Jan 31, 2017 Wed Feb 1, 2017 Thu Feb 2, 2017 Fri Feb 3, 2017 WANT One day later. Roll off "Tue Jan 31, 2017"(oldest) and add "Sat Feb 3,2017"; ================================================================================== * this updates the ring; data genum; retain doc 'This is Sun Feb 5, 2017'; run;quit; Run exactly the same proc print code but drop off the oldest; proc print data=genum(gennum=-3);run;quit; proc print data=genum(gennum=-2);run;quit; proc print data=genum(gennum=-1);run;quit; proc print data=genum(gennum;run;quit; Wed Feb 1, 2017 Thu Feb 2, 2017 Fri Feb 3, 2017 Sat Feb 4, 2017 SOLUTION ======== proc datasets lib=work kill; run;quit; * prime the ring this is a base not interested in this one; data genum(genmax=4); retain doc 'This is Tue Jan 31, 2017'; run;quit; data genum; retain doc 'This is Wed Feb 1, 2017'; run;quit; data genum; retain doc 'This is Thu Feb 2, 2017'; run;quit; data genum; retain doc 'This is Fri Feb 3, 2017'; run;quit; proc contents data=work._all_; run;quit; /* Gen Member # Name Num Type 1 GENUM DATA 2 GENUM -3 DATA 3 GENUM -2 DATA 4 GENUM -2 DATA */ Now lets roll the oldest one off and todays (same code) ======================================================= data genum; retain doc 'This is Sat Feb 4, 2017'; run;quit; * lets see what happend; proc contents data=work._all_; run;quit; * new ring; proc report data=genum(gennum=-3); run;quit; proc report data=genum(gennum=-2); run;quit; proc report data=genum(gennum=-1); run;quit; proc report data=genum; run;quit; This is Wed Feb 1, 2017 This is Thu Feb 2, 2017 This is Fri Feb 3, 2017 This is Sat Feb 4, 2017 Do it again (same code) =========== data genum; retain doc 'This is Sun Feb 5, 2017'; run;quit; proc contents data=work._all_; run;quit; * new ring; proc report data=genum(gennum=-3); run;quit; proc report data=genum(gennum=-2); run;quit; proc report data=genum(gennum=-1); run;quit; proc report data=genum; run;quit; This is Thu Feb 2, 2017 This is Fri Feb 3, 2017 This is Sat Feb 4, 2017 This is Sat Feb 4, 2017 ... View more

Related post If you are using the 'old text editor' the command 'cuth' will change mutiple blanks to one blank. /* T000800 Old text editor macro to compress mutiple blanks to one blank in editor (like compbl) Old text editor macro to compress mutiple blanks to one blank in editor (like compbl) HAVE ==== INPUT ID DATE ABREV COUNT REGION SALES 297945203711 2009.08.31 MO 1 KS 60 297945203711 2009.08.31 MO 2 KU 50 297945303711 2009.08.31 SS 1 MA 60 297945303711 2009.08.31 SS 1 KS 60 297945403711 2009.08.31 JO 1 HB NA WANT ==== INPUT ID DATE ABREV COUNT REGION SALES 297945203711 2009.08.31 MO 1 KS 60 297945203711 2009.08.31 MO 2 KU 50 297945303711 2009.08.31 SS 1 MA 60 297945303711 2009.08.31 SS 1 KS 60 297945403711 2009.08.31 JO 1 HB NA WORKING CODE (may work in enhanced editor?) =========================================== %do i=1 %to 20; c ' ' ' ' all; %end; DETAILS ======= Highlight the code where you want to change mutiple blanks to one blank Type cuth on the command line ie Command ==> cuth 00001 INPUT ID DATE ABREV COUNT REGION SALES 00002 297945203711 2009.08.31 MO 1 KS 60 00003 297945203711 2009.08.31 MO 2 KU 50 00004 297945303711 2009.08.31 SS 1 MA 60 00005 297945303711 2009.08.31 SS 1 KS 60 00006 297945403711 2009.08.31 JO 1 HB NA FULL SOLUTION ============= PUT THE MACRO BELOW IN YOUR AUTOCALL FOLDER. Make sure CMDMAC is turned in. options cmdmac; Highlight the lines you want to compress and type 'cuth' on the commandline. %macro cuth/cmd; %do i=1 %to 20; c ' ' ' ' all; %end; %mend cutout; ... View more

If you have IML interface to R you can use google tesseract. This is the tool google uses to digitize books. Below are two methods, one for jpeg,tiff,png,bmp, gif and pdf(if image). And a second one for pdf with embedded text. /* T0099610 OCR using SAS/WPS and Tesseract-OCR (state of the art google offering) If you have IML interface to R you can use google tesseract. This is the tool google uses to digitize books. OCR using SAS and Tesseract-OCR (state of the art google offering) Adminstrative information Where I originally got the tesseract package https://github.com/UB-Mannheim/tesseract/wiki There is GUI available and you get a console with both distributions. I did not install the GUI. Also located on my google drive(use this before they do a SAS like enhancement) https://drive.google.com/file/d/0ByX2ii2B0Rq9MmZmVVNjLXpNdkU/view?usp=sharing Need this to handle PDFs Get ghostscript here (note ghostscript can combine PDF files and can convert PDF to TIF. http://www.ghostscript.com/download/gsdnld.html Really only need one executable. =============================================== HAVE SIX IMAGE FILES I NEED TO EXTRACT THE TEXT I have these image files png.png bmp.bmp jpg.jpg tiff.tiff gif.gif pdftif.tiff (you need to convert the PDF to an Image file) IMAGE LOOKS LIKE (from proc gslide) MYSTUDY C304456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTE2 NOTE3 NOTE PGM: C:\Tut\Tut_GrfTwoWthTtl.sas OUT: C:\Tut\Tut_GrfTwoWthTtl.pdf - 16AUG16 06:21 ================================================ WANT ( All of them converted to text) MYSTUDY C304456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTE2 NOTE3 NOTE PGM: C:\Tut\Tut_GrfTwoWthTtl.sas OUT: C:\Tut\Tut_GrfTwoWthTtl.pdf - 16AUG16 06:21 SOLUTION Basically it is one command and two commands for a PDF. * conver to text example - bmp to text; x c:/progra~2/tesseract-ocr/tesseract d:/tesser/slide.bmp d:/tesser/slide.txt; Note teseract-OCR is not purfect? Create the image files and convert to text using %macro tesser(device=png300,type=png); filename outfile "d:\tesser\&type..&type"; goptions reset=all rotate=portrait gsfmode = replace device = &device gsfname = outfile vsize=10in hsize=8in htext=2 ; run;quit; proc gslide ; title1 j=l h=2 font='Simplex' "MYSTUDY C04456" j=r "AJAX"; title2 j=l h=2 font='Couier' "DRAFT" j=r "VER 1.0"; title3 j=l h=2 " "; title4 j=c h=3.0 font='Helvetica' "Ajax Study"; title5 j=c h=3.0 font='Arial' "Dose and Placebo"; note j=c h=5 "NOTE1"; note j=c h=5 "NOTE2"; note j=c h=5 "NOTE3"; note j=c h=5 "NOTE"; footnote1 j=l h=2 font='Times Roman' "PGM: C:\Tut\Tut_GrfTwoWthTtl.sas "; footnote2 j=l h=2 font='Helvetica' "OUT: C:\Tut\Tut_GrfTwoWthTtl.pdf - &sysdate &systime"; run; quit; goptions reset=all; filename outfile clear; run;quit; *** ***** ***** * * ***** * * * * * * * * * * * * * ** * **** * * * * * * * * * * * * * * ***** * ***** * * * #! 2aTEXT ; * convert to text; x c:/progra~2/tesseract-ocr/tesseract d:/tesser/&type..&type d:/tesser/&type..txt; %mend tesser; %tesser(device=png300,type=png); %tesser(device=jpeg300,type=jpg); %tesser(device=bmp,type=bmp); %tesser(device=tiff,type=tiff); %tesser(device=gif,type=gif); * Here is how to handle a PDF; Note pdf's often contain embedded fonts so it is better to use another tool to extract the text. There are many free tools acrobat(even reader?), pdfwriter and pdf2text, boxoft and others in R nd Python. You can convert the pdf to an image using free goshtscript at http://www.ghostscript.com/download/gsdnld.html. You really only need gswin64.exe. Converting a pdf to a tiff and ectracting the text using tesseract, x C:\Progra~1\gs\gs9.19\bin\gswin64.exe -dNOPAUSE -dBATCH -sDEVICE=tiffg4 -sOutputFile=d:/tesser/pdftif.tiff d:/tesser/pdf.pdf; x c:/progra~2/tesseract-ocr/tesseract d:/tesser/pdftif.tiff d:/tesser/pdftif.txt; TEXT files I created (not perfect and each may be a little different /* **** * * *** * * ** * * * * * * * * * **** * ** * *** * * * * * * * * * * * * * *** #! PNG ; d:/tesser/png.txt MYSTU DY CO4456 AJAX DRAFT VER 1 .0 Ajax Study Dose and Placebo NOTE1 NOTEZ NOTES NOTE PGM: C:\| ut\l 961w§ththas OUT: C:\Tut\Tut_GrF|'wo_VVthTtl.pdf - 16AUG16 06:21 * **** *** * * * * * * * * * * **** * *** * * * * * * * * * *** * *** #! JPG ; MYSTUDY CO4456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTEZ NOTE3 NOTE PGM: C:\Tut\Tut_GrfTwthhTtl.sas OUT: C:\Tut\Tut_GrfTwthhTtl.pdf - 16AUG16 06:21 MYSTUDY (304456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTEZ NOTES NOTE PGM: C:\| ut\l 961w§ththas OUT: C:\Tut\Tut_GrF|'wo_VVthTtl.pdf - 16AUG16 06:21 **** **** ***** ***** ***** ***** * * * * * * * * * * * * * * * * **** * * **** * * **** * * * * * * * * * * * * * * * **** * * ***** * MYSTUDY CO4456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTE2 NOTE3 NOTE PGM: C:\Tut\Tut_GrfTwthhTtl.sas OUT: C:\Tut\Tut_GrfTwthhTtl.pdf - 16AUG16 06:21 **** * * **** * * ** ** * * * * * * * * * *** * * **** * * * * * * * * * * **** * * * #! BMP ; MYSTUDY CO4456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTEZ NOTE3 NOTE PGM: C:\Tut\Tut_GrfTwthhTtl.sas OUT: C:\Tut\Tut_GrfTwthhTtl.pdf - 16AUG16 06:21 ***** ***** ***** ***** * * * * * * * * * * **** **** * * * * * * * * * ***** * * #! TIFF ; MYSTUDY (304456 AJAX DRAFT VER 1.0 Ajax Study Dose and Placebo NOTE1 NOTEZ NOTES PGM: C:\| ut\l 961w§ththas OUT: C:\Tut\Tut_GrF|'wo_VVthTtl.pdf - 16AUG16 06:21 *** ***** ***** * * * * * * * * *** * **** * * * * * * * * *** ***** * #! GIF ; MYSTU DY CO4456 AJAX DRAFT VER 1 .0 Ajax Study Dose and Placebo NOTE1 NOTE2 NOTE3 NOTE PGM: C:\Tut\Tut_Gn‘TwthhTt|.sas OUT: C:\Tut\Tut_Gn‘TwthhTt|.pdf - 16AUG16 06:21 MYSTU DY CO4456 AJAX DRAFT VER 1 .0 Ajax Study Dose and Placebo NOTE1 NOTE2 NOTE3 NOTE PGM: C:\Tut\Tut_Gn‘TwthhTt|.sas OUT: C:\Tut\Tut_Gn‘TwthhTt|.pdf - 16AUG16 06:21 /* T1001390 SAS/R: Coverting PDF tables to SAS datasets (simple example) WPS/SAS/R: Coverting PDF tables to SAS datasets (simple example) There are more options in the TM (text mining package) HAVE ( PDF file with the table below) ====================================== NAME SEX AGE HEIGHT WEIGHT Alfred M 14 69 112.5 Alice F 13 56.5 84 Barbara F 13 65.3 98 Carol F 14 62.8 102.5 Henry M 14 63.5 102.5 James M 12 57.3 83 Jane F 12 59.8 84.5 Janet F 15 62.5 112.5 Jeffrey M 13 62.5 84 John M 12 59 99.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 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 WANT (SAS dataset) =================== Up to 40 obs from sashelp.class total obs=19 Obs NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69 112.5 2 Alice F 13 56.5 84 3 Barbara F 13 65.3 98 4 Carol F 14 62.8 102.5 5 Henry M 14 63.5 102.5 6 James M 12 57.3 83 7 Jane F 12 59.8 84.5 8 Janet F 15 62.5 112.5 9 Jeffrey M 13 62.5 84 10 John M 12 59 99.5 11 Joyce F 11 51.3 50.5 12 Judy F 14 64.3 90 13 Louise F 12 56.3 77 14 Mary F 15 66.5 112 15 Philip M 16 72 150 16 Robert M 12 64.8 128 17 Ronald M 15 67 133 18 Thomas M 11 57.5 85 19 William M 15 66.5 112 WORKING CODE ============ file <- "d:/pdf/class.pdf"; Rpdf <- readPDF(control = list(text = "-layout")); corpus <- VCorpus(URISource(file), readerControl = list(reader = Rpdf)); classtext <- as.data.frame(content(content(corpus)[[1]])); FULL SOLUTION ============= * create a pdf; title;footnote; ods pdf file="d:/pdf/class.pdf"; proc print data=sashelp.class noobs; run;quit; ods pdf close; * xpdf executables have to be in the path; %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); library("tm"); library("slam"); file <- "d:/pdf/class.pdf"; Rpdf <- readPDF(control = list(text = "-layout")); corpus <- VCorpus(URISource(file), readerControl = list(reader = Rpdf)); array <- as.data.frame(content(content(corpus)[[1]])); colnames(array)<-"lines"; endsubmit; import r=array data=wrk.array; run;quit; '); proc print data=array(where=(lines ne ' ')) width=min; run;quit; Obs LINES 1 NAME SEX AGE HEIGHT WEIGHT 3 Alfred M 14 69.0 112.5 5 Alice F 13 56.5 84.0 7 Barbara F 13 65.3 98.0 9 Carol F 14 62.8 102.5 11 Henry M 14 63.5 102.5 13 James M 12 57.3 83.0 15 Jane F 12 59.8 84.5 17 Janet F 15 62.5 112.5 19 Jeffrey M 13 62.5 84.0 21 John M 12 59.0 99.5 23 Joyce F 11 51.3 50.5 25 Judy F 14 64.3 90.0 27 Louise F 12 56.3 77.0 29 Mary F 15 66.5 112.0 31 Philip M 16 72.0 150.0 33 Robert M 12 64.8 128.0 35 Ronald M 15 67.0 133.0 37 Thomas M 11 57.5 85.0 39 William M 15 66.5 112.0 ... View more