Computing the mode by row (combination of R and R) HAVE Need the mode for variables e1-e3 and b0-b5 in a new column (mode_e, mode_b). If multimode, select one randomly. If unique select select one randomly. Keep all the variables in the final output. The code must be able to compute this for large database. The R code below can process about 100,000 records a minute on my 2008 T7400(64gb ram) running 8 jobs in parallel. 10 minutes to do 1 million, 1000 minutes to 100 millon (still small scale). Too long to get to big data (ie 10 billion +). 10 billion would take 100,000 minutes. (100000/1440) = 69 days. SAS should be faster, but not as elgant(maybe IML)?. The solutiion below will not scale to big data (ie 10 billion+ rows) Maybe it will work work with millions of rows * added a key - I like I primary key; data "d:/sd1/df_a.sas7bdat" ; retain id obs key 0; input id obs e1 e2 e3 b0 b1 b2 b3 b4 b5; key=_n_; datalines; 2 2 1 2 3 600 0 200 0 530 550 2 1 1 2 3 600 0 200 0 530 550 3 3 2 2 3 150 500 . 120 3 58 3 2 2 2 3 150 500 . 120 3 58 3 1 2 2 1 150 500 . 120 3 58 4 3 1 2 3 200 300 550 270 0 50 4 2 1 2 3 200 300 550 270 0 50 4 1 1 2 3 200 300 550 270 0 50 5 1 1 2 . 20 20 200 200 500 57 5 2 1 2 . 20 20 200 200 500 57 5 3 1 2 . 20 20 200 200 500 57 run;quit; HAVE /* Up to 40 obs WC000001.DF_A total obs=11 ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 2 2 1 1 2 3 600 0 200 0 530 550 2 1 2 1 2 3 600 0 200 0 530 550 3 3 3 2 2 3 150 500 . 120 3 58 3 2 4 2 2 3 150 500 . 120 3 58 3 1 5 2 2 1 150 500 . 120 3 58 4 3 6 1 2 3 200 300 550 270 0 50 4 2 7 1 2 3 200 300 550 270 0 50 4 1 8 1 2 3 200 300 550 270 0 50 5 1 9 1 2 . 20 20 200 100 500 57 5 2 10 1 2 . 20 20 200 100 500 57 5 3 11 1 2 . 20 20 200 100 500 57 WANT Up to 40 obs from xpt.keyres total obs=&tob ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 MODE_E MODE_B 2 2 1 1 2 3 600 0 200 0 530 550 1 0 single mode 2 1 2 1 2 3 600 0 200 0 530 550 2 0 single mode 3 3 3 2 2 3 150 500 . 120 3 58 3 3 random 3 2 4 2 2 3 150 500 . 120 3 58 2 500 random 3 1 5 2 2 1 150 500 . 120 3 58 2 3 random 4 3 6 1 2 3 200 300 550 270 0 50 6 300 random 4 2 7 1 2 3 200 300 550 270 0 50 7 270 random 4 1 8 1 2 3 200 300 550 270 0 50 2 270 random 5 1 9 1 2 . 20 20 200 200 500 57 2 20 multimode(random) 5 2 10 1 2 . 20 20 200 200 500 57 2 200 multimode(random) 5 3 11 1 2 . 20 20 200 200 500 57 2 20 multimode(random) */ SOLUTION /* Using list lapply find max count of duplicates Get the values corresponding to max counts Sample random one of multiple modes Sample random one if no mode (all values distinct) Sample has an issue when there is only one valee so I use resamp */ %utl_submit_r( library(haven); library(SASxport); datcut<-as.matrix(read_sas('d:/sd1/df_a.sas7bdat')); /* Begin R solution */ resamp <- function(x){if (length(x)==1) {x} else {sample(x,1)}}; rmode <- function(x) { x <- sort(x); u <- unique(x); if (length(x) == length(u)) {resamp(x)} else { y <- lapply(u, function(y) length(x[x==y])); resamp(u[which( unlist(y) == max(unlist(y)))]); } }; mode_e<-apply(datcut[,c(4:6)],1,rmode); mode_b<-apply(datcut[,c(7:11)],1,rmode); keyres=as.data.frame(cbind(datcut,as.data.frame(cbind(mode_e,mode_b)))); keyres; write.xport(keyres,file='d:/xpt/xfr&set..xpt'); )); /* R LOG > library(haven); library(SASxport); datcut<-as.matrix(read_sas('d:/sd1/df_a.sas7bdat')); resamp <- function(x){if (length(x)==1) {x} else {sample(x,1)}}; rmode <- functi on(x) { x <- sort(x); u <- unique(x); y <- lapply(u, function(y) length(x[x==y])); u[which( unlist(y) == max(unlist(y)))]; }; mode_e<-unlist(lapply(apply(datcut[,c (4:6)],1,rmode),resamp)); mode_b<-unlist(l ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 mode_e mode_b 1 2 2 1 1 2 3 600 0 200 0 530 550 2 0 2 2 1 2 1 2 3 600 0 200 0 530 550 1 0 3 3 3 3 2 2 3 150 500 NA 120 3 58 2 150 Random 4 3 2 4 2 2 3 150 500 NA 120 3 58 2 120 Random 5 3 1 5 2 2 1 150 500 NA 120 3 58 2 500 Random 6 4 3 6 1 2 3 200 300 550 270 0 50 2 0 Random 7 4 2 7 1 2 3 200 300 550 270 0 50 1 270 Random 8 4 1 8 1 2 3 200 300 550 270 0 50 1 270 Random 9 5 1 9 1 2 NA 20 20 200 200 500 57 1 200 multimode 10 5 2 10 1 2 NA 20 20 200 200 500 57 2 20 multimode 11 5 3 11 1 2 NA 20 20 200 200 500 57 2 200 multimode */ libname xpt xport 'd:/xpt/xfr.xpt'; proc print data=xpt.keyres; ;run;quit; libname xpt clear; /* SAS Proc print ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 MODE_E MODE_B 2 2 1 1 2 3 600 0 200 0 530 550 2 0 2 1 2 1 2 3 600 0 200 0 530 550 1 0 3 3 3 2 2 3 150 500 . 120 3 58 2 150 3 2 4 2 2 3 150 500 . 120 3 58 2 120 3 1 5 2 2 1 150 500 . 120 3 58 2 500 4 3 6 1 2 3 200 300 550 270 0 50 2 0 4 2 7 1 2 3 200 300 550 270 0 50 1 270 4 1 8 1 2 3 200 300 550 270 0 50 1 270 5 1 9 1 2 . 20 20 200 100 500 57 1 200 5 2 10 1 2 . 20 20 200 100 500 57 2 20 5 3 11 1 2 . 20 20 200 100 500 57 2 200 */ SCALE THE PROBLEM * This solution only scales well for small data, in the millions; * Create a very small dataset of 400,000 obs; * We will run 8 parallel jobs so lets split the 400,000 observations into 8 pieces of 50,000 obs each; * Create a small datasets about 400,000 obs; * 400,000; data "d:/wrk/datcut.sas7bdat"; retain key 0; set "d:/sd1/df_a.sas7bdat"(drop=key obs=10); do i=1 to 40000; key=key+1; output; end; drop i; ;run;quit; /* Up to 40 obs "d:/wrk/datcut.sas7bdat" total obs=400,000 KEY ID OBS E1 E2 E3 B0 B1 B2 B3 B4 B5 1 2 2 1 2 3 600 0 200 0 530 550 2 2 2 1 2 3 600 0 200 0 530 550 3 2 2 1 2 3 600 0 200 0 530 550 4 2 2 1 2 3 600 0 200 0 530 550 5 2 2 1 2 3 600 0 200 0 530 550 6 2 2 1 2 3 600 0 200 0 530 550 7 2 2 1 2 3 600 0 200 0 530 550 8 2 2 1 2 3 600 0 200 0 530 550 */ %macro getmode(set,fyl=d:/wrk/datcut.sas7bdat,span=40000); /* %let set=5; %let fyl=d:/wrk/datcut.sas7bdat; %let span=5000; */ %local b e; %let e=%sysevalf(&set * &span ); %let b=%sysevalf((&set - 1)*&span +1); %put Stary &=b; %put END &=e; * grab 50,000 subset; data "d:/wrk/subset&set..sas7bdat"; set "&fyl"(firstobs=&b obs=&e); run;quit; %utl_submit_r( library(haven); library(SASxport); datcut<-as.matrix(read_sas('d:/wrk/subset&set..sas7bdat')); /* Begin R solution */ resamp <- function(x){if (length(x)==1) {x} else {sample(x,1)}}; rmode <- function(x) { x <- sort(x); u <- unique(x); if (length(x) == length(u)) {resamp(x)} else { y <- lapply(u, function(y) length(x[x==y])); resamp(u[which( unlist(y) == max(unlist(y)))]); } }; mode_e<-apply(datcut[,c(4:6)],1,rmode); mode_b<-apply(datcut[,c(7:12)],1,rmode); keyres=as.data.frame(cbind(datcut,as.data.frame(cbind(mode_e,mode_b)))); write.xport(keyres,file='d:/xpt/xfr&set..xpt'); ); %mend getmode; * if you tested with let statements inside macro (creates globals); %symdel set b e fyl; %getmode(3); libname xpt xport 'd:/xpt/xfr3.xpt'; proc print data=xpt.keyres(obs=10); ;run;quit; libname xpt clear; %let _s=%sysfunc(compbl(C:\Progra~1\SASHome\SASFoundation\9.4\sas.exe -sysin c:\nul -sasautos c:\oto -autoexec c:\oto\Tut_Oto.sas -work h:\wrk)); options noxwait noxsync; %let tym=%sysfunc(time()); systask kill sys1 sys2 sys3 sys4 sys5 sys6 sys7 sys8; systask command "&_s -termstmt %nrstr(%getmode(1);) -log G:\wrk\sys1.log" taskname=sys1; systask command "&_s -termstmt %nrstr(%getmode(2);) -log G:\wrk\sys2.log" taskname=sys2; systask command "&_s -termstmt %nrstr(%getmode(3);) -log G:\wrk\sys3.log" taskname=sys3; systask command "&_s -termstmt %nrstr(%getmode(4);) -log G:\wrk\sys4.log" taskname=sys4; systask command "&_s -termstmt %nrstr(%getmode(5);) -log G:\wrk\sys5.log" taskname=sys5; systask command "&_s -termstmt %nrstr(%getmode(6);) -log G:\wrk\sys6.log" taskname=sys6; systask command "&_s -termstmt %nrstr(%getmode(7);) -log G:\wrk\sys7.log" taskname=sys7; systask command "&_s -termstmt %nrstr(%getmode(8);) -log G:\wrk\sys8.log" taskname=sys8; waitfor sys1 sys2 sys3 sys4 sys5 sys6 sys7 sys8; %put %sysevalf( %sysfunc(time()) - &tym); /* NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414 NOTE: The SAS System used: real time 4:15.10 Cumulative Cumulative E1 E2 E3 Frequency Percent Frequency Percent ------------------------------------------------------------------- 1 2 . 80000 20.00 80000 20.00 1 2 3 200000 50.00 280000 70.00 2 2 1 40000 10.00 320000 80.00 2 2 3 80000 20.00 400000 100.00 */ %macro vueapp(dummy); %local i; %do i=1 %to 8; libname xpt&i xport "d:/xpt/xfr&i..xpt"; %end; libname sd1 "d:/sd1"; proc datasets lib=sd1 mt=view; delete keyresall; ;run;quit; data sd1.keyresall/view=sd1.keyresall; set xpt1.keyres xpt2.keyres xpt3.keyres xpt4.keyres xpt5.keyres xpt6.keyres xpt7.keyres xpt8.keyres ; ;run;quit; proc freq data=sd1.keyresall; tables e1*e2*e3 / list missing; ;run;quit; %mend vueapp; %vueapp; libname xpt xport 'd:/xpt/xfr8.xpt'; proc print data=sd1.keyresall(where=(uniform(-1)<10/400000)); ;run;quit; libname xpt clear; /* KEY ID OBS E1 E2 E3 B0 B1 B2 B3 B4 B5 MODE_E MODE_B 12384 2 2 1 2 3 600 0 200 0 530 550 2 0 50429 2 1 1 2 3 600 0 200 0 530 550 2 0 54631 2 1 1 2 3 600 0 200 0 530 550 1 0 77263 2 1 1 2 3 600 0 200 0 530 550 2 0 77572 2 1 1 2 3 600 0 200 0 530 550 1 0 130353 3 2 2 2 3 150 500 . 120 3 58 2 3 233744 4 3 1 2 3 200 300 550 270 0 50 2 550 319192 4 1 1 2 3 200 300 550 270 0 50 1 200 */ ... View more

Hi Team     There was atype in my previous heading, should have been R and SAS not R and R.     I ran Xia's code and it took about 6 seconds for 400,000 records. Much faster than R.   Mine took 4 minutes, running in parallel.     The output was the same as mine.   The FREQ Procedure Cumulative Cumulative E1 E2 E3 Frequency Percent Frequency Percent ------------------------------------------------------------------- 1 2 . 80000 20.00 80000 20.00 1 2 3 200000 50.00 280000 70.00 2 2 1 40000 10.00 320000 80.00 2 2 3 80000 20.00 400000 100.00 ... View more

Computing the mode by row (combination of R and R) HAVE Need the mode for variables e1-e3 and b0-b5 in a new column (mode_e, mode_b). If multimode, select one randomly. If unique select select one randomly. Keep all the variables in the final output. The code must be able to compute this for large database. The R code below can process about 100,000 records a minute on my 2008 T7400(64gb ram) running 8 jobs in parallel. 10 minutes to do 1 million, 1000 minutes to 100 millon (still small scale). Too long to get to big data (ie 10 billion +). 10 billion would take 100,000 minutes. (100000/1440) = 69 days. SAS should be faster, but not as elgant(maybe IML)?. The solutiion below will not scale to big data (ie 10 billion+ rows) Maybe it will work work with millions of rows * added a key - I like I primary key; data "d:/sd1/df_a.sas7bdat" ; retain id obs key 0; input id obs e1 e2 e3 b0 b1 b2 b3 b4 b5; key=_n_; datalines; 2 2 1 2 3 600 0 200 0 530 550 2 1 1 2 3 600 0 200 0 530 550 3 3 2 2 3 150 500 . 120 3 58 3 2 2 2 3 150 500 . 120 3 58 3 1 2 2 1 150 500 . 120 3 58 4 3 1 2 3 200 300 550 270 0 50 4 2 1 2 3 200 300 550 270 0 50 4 1 1 2 3 200 300 550 270 0 50 5 1 1 2 . 20 20 200 200 500 57 5 2 1 2 . 20 20 200 200 500 57 5 3 1 2 . 20 20 200 200 500 57 run;quit; HAVE /* Up to 40 obs WC000001.DF_A total obs=11 ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 2 2 1 1 2 3 600 0 200 0 530 550 2 1 2 1 2 3 600 0 200 0 530 550 3 3 3 2 2 3 150 500 . 120 3 58 3 2 4 2 2 3 150 500 . 120 3 58 3 1 5 2 2 1 150 500 . 120 3 58 4 3 6 1 2 3 200 300 550 270 0 50 4 2 7 1 2 3 200 300 550 270 0 50 4 1 8 1 2 3 200 300 550 270 0 50 5 1 9 1 2 . 20 20 200 100 500 57 5 2 10 1 2 . 20 20 200 100 500 57 5 3 11 1 2 . 20 20 200 100 500 57 WANT Up to 40 obs from xpt.keyres total obs=&tob ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 MODE_E MODE_B 2 2 1 1 2 3 600 0 200 0 530 550 1 0 single mode 2 1 2 1 2 3 600 0 200 0 530 550 2 0 single mode 3 3 3 2 2 3 150 500 . 120 3 58 3 3 random 3 2 4 2 2 3 150 500 . 120 3 58 2 500 random 3 1 5 2 2 1 150 500 . 120 3 58 2 3 random 4 3 6 1 2 3 200 300 550 270 0 50 6 300 random 4 2 7 1 2 3 200 300 550 270 0 50 7 270 random 4 1 8 1 2 3 200 300 550 270 0 50 2 270 random 5 1 9 1 2 . 20 20 200 200 500 57 2 20 multimode(random) 5 2 10 1 2 . 20 20 200 200 500 57 2 200 multimode(random) 5 3 11 1 2 . 20 20 200 200 500 57 2 20 multimode(random) */ SOLUTION /* Using list lapply find max count of duplicates Get the values corresponding to max counts Sample random one of multiple modes Sample random one if no mode (all values distinct) Sample has an issue when there is only one valee so I use resamp */ %utl_submit_r( library(haven); library(SASxport); datcut<-as.matrix(read_sas('d:/sd1/df_a.sas7bdat')); /* Begin R solution */ resamp <- function(x){if (length(x)==1) {x} else {sample(x,1)}}; rmode <- function(x) { x <- sort(x); u <- unique(x); if (length(x) == length(u)) {resamp(x)} else { y <- lapply(u, function(y) length(x[x==y])); resamp(u[which( unlist(y) == max(unlist(y)))]); } }; mode_e<-apply(datcut[,c(4:6)],1,rmode); mode_b<-apply(datcut[,c(7:11)],1,rmode); keyres=as.data.frame(cbind(datcut,as.data.frame(cbind(mode_e,mode_b)))); keyres; write.xport(keyres,file='d:/xpt/xfr&set..xpt'); )); /* R LOG > library(haven); library(SASxport); datcut<-as.matrix(read_sas('d:/sd1/df_a.sas7bdat')); resamp <- function(x){if (length(x)==1) {x} else {sample(x,1)}}; rmode <- functi on(x) { x <- sort(x); u <- unique(x); y <- lapply(u, function(y) length(x[x==y])); u[which( unlist(y) == max(unlist(y)))]; }; mode_e<-unlist(lapply(apply(datcut[,c (4:6)],1,rmode),resamp)); mode_b<-unlist(l ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 mode_e mode_b 1 2 2 1 1 2 3 600 0 200 0 530 550 2 0 2 2 1 2 1 2 3 600 0 200 0 530 550 1 0 3 3 3 3 2 2 3 150 500 NA 120 3 58 2 150 Random 4 3 2 4 2 2 3 150 500 NA 120 3 58 2 120 Random 5 3 1 5 2 2 1 150 500 NA 120 3 58 2 500 Random 6 4 3 6 1 2 3 200 300 550 270 0 50 2 0 Random 7 4 2 7 1 2 3 200 300 550 270 0 50 1 270 Random 8 4 1 8 1 2 3 200 300 550 270 0 50 1 270 Random 9 5 1 9 1 2 NA 20 20 200 200 500 57 1 200 multimode 10 5 2 10 1 2 NA 20 20 200 200 500 57 2 20 multimode 11 5 3 11 1 2 NA 20 20 200 200 500 57 2 200 multimode */ libname xpt xport 'd:/xpt/xfr.xpt'; proc print data=xpt.keyres; ;run;quit; libname xpt clear; /* SAS Proc print ID OBS KEY E1 E2 E3 B0 B1 B2 B3 B4 B5 MODE_E MODE_B 2 2 1 1 2 3 600 0 200 0 530 550 2 0 2 1 2 1 2 3 600 0 200 0 530 550 1 0 3 3 3 2 2 3 150 500 . 120 3 58 2 150 3 2 4 2 2 3 150 500 . 120 3 58 2 120 3 1 5 2 2 1 150 500 . 120 3 58 2 500 4 3 6 1 2 3 200 300 550 270 0 50 2 0 4 2 7 1 2 3 200 300 550 270 0 50 1 270 4 1 8 1 2 3 200 300 550 270 0 50 1 270 5 1 9 1 2 . 20 20 200 100 500 57 1 200 5 2 10 1 2 . 20 20 200 100 500 57 2 20 5 3 11 1 2 . 20 20 200 100 500 57 2 200 */ SCALE THE PROBLEM * This solution only scales well for small data, in the millions; * Create a very small dataset of 400,000 obs; * We will run 8 parallel jobs so lets split the 400,000 observations into 8 pieces of 50,000 obs each; * Create a small datasets about 400,000 obs; * 400,000; data "d:/wrk/datcut.sas7bdat"; retain key 0; set "d:/sd1/df_a.sas7bdat"(drop=key obs=10); do i=1 to 40000; key=key+1; output; end; drop i; ;run;quit; /* Up to 40 obs "d:/wrk/datcut.sas7bdat" total obs=400,000 KEY ID OBS E1 E2 E3 B0 B1 B2 B3 B4 B5 1 2 2 1 2 3 600 0 200 0 530 550 2 2 2 1 2 3 600 0 200 0 530 550 3 2 2 1 2 3 600 0 200 0 530 550 4 2 2 1 2 3 600 0 200 0 530 550 5 2 2 1 2 3 600 0 200 0 530 550 6 2 2 1 2 3 600 0 200 0 530 550 7 2 2 1 2 3 600 0 200 0 530 550 8 2 2 1 2 3 600 0 200 0 530 550 */ %macro getmode(set,fyl=d:/wrk/datcut.sas7bdat,span=40000); /* %let set=5; %let fyl=d:/wrk/datcut.sas7bdat; %let span=5000; */ %local b e; %let e=%sysevalf(&set * &span ); %let b=%sysevalf((&set - 1)*&span +1); %put Stary &=b; %put END &=e; * grab 50,000 subset; data "d:/wrk/subset&set..sas7bdat"; set "&fyl"(firstobs=&b obs=&e); run;quit; %utl_submit_r( library(haven); library(SASxport); datcut<-as.matrix(read_sas('d:/wrk/subset&set..sas7bdat')); /* Begin R solution */ resamp <- function(x){if (length(x)==1) {x} else {sample(x,1)}}; rmode <- function(x) { x <- sort(x); u <- unique(x); if (length(x) == length(u)) {resamp(x)} else { y <- lapply(u, function(y) length(x[x==y])); resamp(u[which( unlist(y) == max(unlist(y)))]); } }; mode_e<-apply(datcut[,c(4:6)],1,rmode); mode_b<-apply(datcut[,c(7:12)],1,rmode); keyres=as.data.frame(cbind(datcut,as.data.frame(cbind(mode_e,mode_b)))); write.xport(keyres,file='d:/xpt/xfr&set..xpt'); ); %mend getmode; * if you tested with let statements inside macro (creates globals); %symdel set b e fyl; %getmode(3); libname xpt xport 'd:/xpt/xfr3.xpt'; proc print data=xpt.keyres(obs=10); ;run;quit; libname xpt clear; %let _s=%sysfunc(compbl(C:\Progra~1\SASHome\SASFoundation\9.4\sas.exe -sysin c:\nul -sasautos c:\oto -autoexec c:\oto\Tut_Oto.sas -work h:\wrk)); options noxwait noxsync; %let tym=%sysfunc(time()); systask kill sys1 sys2 sys3 sys4 sys5 sys6 sys7 sys8; systask command "&_s -termstmt %nrstr(%getmode(1);) -log G:\wrk\sys1.log" taskname=sys1; systask command "&_s -termstmt %nrstr(%getmode(2);) -log G:\wrk\sys2.log" taskname=sys2; systask command "&_s -termstmt %nrstr(%getmode(3);) -log G:\wrk\sys3.log" taskname=sys3; systask command "&_s -termstmt %nrstr(%getmode(4);) -log G:\wrk\sys4.log" taskname=sys4; systask command "&_s -termstmt %nrstr(%getmode(5);) -log G:\wrk\sys5.log" taskname=sys5; systask command "&_s -termstmt %nrstr(%getmode(6);) -log G:\wrk\sys6.log" taskname=sys6; systask command "&_s -termstmt %nrstr(%getmode(7);) -log G:\wrk\sys7.log" taskname=sys7; systask command "&_s -termstmt %nrstr(%getmode(8);) -log G:\wrk\sys8.log" taskname=sys8; waitfor sys1 sys2 sys3 sys4 sys5 sys6 sys7 sys8; %put %sysevalf( %sysfunc(time()) - &tym); /* NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414 NOTE: The SAS System used: real time 4:15.10 Cumulative Cumulative E1 E2 E3 Frequency Percent Frequency Percent ------------------------------------------------------------------- 1 2 . 80000 20.00 80000 20.00 1 2 3 200000 50.00 280000 70.00 2 2 1 40000 10.00 320000 80.00 2 2 3 80000 20.00 400000 100.00 */ %macro vueapp(dummy); %local i; %do i=1 %to 8; libname xpt&i xport "d:/xpt/xfr&i..xpt"; %end; libname sd1 "d:/sd1"; proc datasets lib=sd1 mt=view; delete keyresall; ;run;quit; data sd1.keyresall/view=sd1.keyresall; set xpt1.keyres xpt2.keyres xpt3.keyres xpt4.keyres xpt5.keyres xpt6.keyres xpt7.keyres xpt8.keyres ; ;run;quit; proc freq data=sd1.keyresall; tables e1*e2*e3 / list missing; ;run;quit; %mend vueapp; %vueapp; libname xpt xport 'd:/xpt/xfr8.xpt'; proc print data=sd1.keyresall(where=(uniform(-1)<10/400000)); ;run;quit; libname xpt clear; /* KEY ID OBS E1 E2 E3 B0 B1 B2 B3 B4 B5 MODE_E MODE_B 12384 2 2 1 2 3 600 0 200 0 530 550 2 0 50429 2 1 1 2 3 600 0 200 0 530 550 2 0 54631 2 1 1 2 3 600 0 200 0 530 550 1 0 77263 2 1 1 2 3 600 0 200 0 530 550 2 0 77572 2 1 1 2 3 600 0 200 0 530 550 1 0 130353 3 2 2 2 3 150 500 . 120 3 58 2 3 233744 4 3 1 2 3 200 300 550 270 0 50 2 550 319192 4 1 1 2 3 200 300 550 270 0 50 1 200 */ ... View more

Excel: Replacing data in a named range while maintaing formatting without SAS Access to PC Files This post calls R to replace data in the named range. You may need full SAS for this, especially if you are locked out of a an operating system. HAVE * create excel file with named range "class"; * I am doing this to provide a excel file with a named range; * you already have the a excel sheet with named range; * create named range "class"; %utlfkil(d:\xls\class.xlsx); * delete if exists; libname xls "d:\xls\class.xlsx"; data xls.class; set sashelp.class; ;run;quit; proc contents data=xls._all_; ;run;quit; libname xls clear; /* Libref XLS Engine EXCEL Physical Name d:\xls\class.xlsx User Admin DBMS Member Obs, Entries Member # Name Type or Indexes Vars Label Type 1 class DATA . 5 TABLE 2 class$ DATA . 5 TABLE to 40 obs from d:\xls\class.xlsx total obs=19 Rows 1-6 are highlighted in yellow 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 .... */ WANT (with rows 1-6 highlighted in yellow - retained) (sorted by descending name) NAME SEX AGE HEIGHT WEIGHT 1 William M 15 66.5 112.0 2 Thomas M 11 57.5 85.0 3 Ronald M 15 67.0 133.0 4 Robert M 12 64.8 128.0 5 Philip M 16 72.0 150.0 SOLUTION * Create some replacement data; * dataset to overwrite named region data keeping your customizations; proc sort data=sashelp.class out="d:/sd1/newclass.sas7bdat"; by descending name; run;quit; * overwite data in named range class; %utl_submit_r64(%nrbquote( library(XLConnect); library(haven); newclass<-read_sas("d:/sd1/newclass.sas7bdat"); wb <- loadWorkbook("d:/xls/class.xlsx"); /* set Style says to retain formatting */ setStyleAction(wb,XLC$"STYLE_ACTION.NONE"); writeNamedRegion(wb,newclass,name="class"); saveWorkbook(wb); )); %macro utl_submit_R64(pgmx)/des="Semi colon separated set of R commands"; * write the program to a temporary file; filename r_pgm temp lrecl=32766 recfm=v; data _null_; file r_pgm; pgm=compbl("&pgmx"); put pgm; putlog pgm; run; %let __loc=%sysfunc(pathname(r_pgm)); * pipe file through R; filename rut pipe "c:\Progra~1\R\R-3.2.4\bin\x64\R --quiet --no-save < &__loc"; data _null_; file print; infile rut; input; put _infile_; putlog _infile_; run; filename rut clear; filename r_pgm clear; %mend utl_submit_r64; ... View more

HAVE AN EXCEL SHEET and I need to input merged headers and merged data rows * I think you need full SAS for this because only 'proc export' works in the reduced SAS enviroment. This solution reads merged headers and merged row cells. Merged headers within the data portion that do not start in column 1 are an issue. You need to know the starting cell of the interior merged header and read the ranges above and below the interior merged header. You may be able to use DBSASTYPE to change a interior column type to a long character and avoid multiple range reads. HAVE * create a sheet with two merged headers and one merged row og data; %utlfkil(d:/xls/mrgcls.xlsx); * delete if exists; ods excel file="d:/xls/mrgcls.xlsx" ; ods excel options(sheet_name="mrgcls" sheet_interval="none"); proc report data=sashelp.class missing; COLUMN ("This is a merged cell" NAME AGE ("Lab values" HEIGHT WEIGHT)); define name / group ; break before name / skip; compute before name ; txt='MERGED'; if name='Carol' then len=10; else len=0; line txt $varying. len; endcomp; run;quit; ods excel close; * merged cells are tricky especially if they occur randomly; * however if you know where they are you can extract them. /* HAVE This is a merged cell Lab values NAME AGE HEIGHT WEIGHT Alfred 14 69 112.5 Alice 13 56.5 84 Barbara 13 65.3 98 MERGED Carol 14 62.8 102.5 Henry 14 63.5 102.5 James 12 57.3 83 Jane 12 59.8 84.5 Janet 15 62.5 112.5 */ WANT HEADER GET Merged Headers Up to 40 obs WORK.HEADER total obs=2 Obs F1 F2 F3 F4 1 This is a merged cell 2 Lab values ... DATA Get Merged data Up to 40 obs WORK.DATA total obs=20 Obs NAME AGE HEIGHT WEIGHT 1 Alfred 14 69.0 112.5 2 Alice 13 56.5 84.0 3 Barbara 13 65.3 98.0 4 MERGED . . . 5 Carol 14 62.8 102.5 6 Henry 14 63.5 102.5 7 James 12 57.3 83.0 8 Jane 12 59.8 84.5 9 Janet 15 62.5 112.5 ... SOLUTION libname xls 'd:/xls/mrgcls.xlsx' header=no scantext=no; data header; set xls.'mrgcls$A1:E2'n; ;run;quit; libname close; libname xls 'd:/xls/mrgcls.xlsx'; data data; set xls.'mrgcls$A3:E9999'n; /* over specify */ ;run;quit; libname xls clear;   ... View more

Along the lines of saving you password protected file to a restricted access folder without excel passwords, the VBA script below will remove the workbook and worksheet passwords   If you have full local SAS you should be able to execute this code from SAS or SAS calling R.   Sub ChDirNet(szPath As String) SetCurrentDirectoryA szPath End Sub Sub RemovePasswords() Dim SaveDriveDir As String Dim FName As Variant Dim FNum As Long Dim mybook As Workbook, ws As Worksheet Dim workbookpass As String Dim sheetpass As String workbookpass = ThisWorkbook.Sheets(1).Range("B1").Value sheetpass = ThisWorkbook.Sheets(1).Range("B2").Value SaveDriveDir = CurDir ChDir ThisWorkbook.Path FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then For FNum = LBound(FName) To UBound(FName) Set mybook = Nothing Set mybook = Workbooks.Open(FName(FNum), UpdateLinks:=0, ReadOnly:=False, Password:=workbookpass) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next With mybook mybook.Unprotect Password:=sheetpass For Each ws In Worksheets ws.Unprotect Password:=sheetpass Next ws End With Application.DisplayAlerts = False mybook.SaveAs Password:="" mybook.Close Application.DisplayAlerts = True End If Next FNum End If End Sub ... View more

Hi Team Another possible solution? * You need full SAS for this; * wish sas would deprecate 'proc import/export' and beef up the excel engines, they are potentially more powerfull, especially when used with passthru; HAVE Excel sheets (from SASHELP.CARS) XLS.'_asia$'n. XLS.'_europe$'n. XLS.'_usa$'n. WANT (create single dataset SASHELP.CARS) Middle Observation(214 ) of WORK.CARS - Total Obs 428 -- CHARACTER -- MAKE C 13 Kia MODEL C 40 Sedona LX TYPE C 8 Sedan ORIGIN C 6 Asia DRIVETRAIN C 5 Front -- NUMERIC -- MSRP N 8 20615 INVOICE N 8 19400 ENGINESIZE N 8 3.5 CYLINDERS N 8 6 HORSEPOWER N 8 195 MPG_CITY N 8 16 MPG_HIGHWAY N 8 22 WEIGHT N 8 4802 WHEELBASE N 8 115 LENGTH N 8 194 * below I create a excel workbook with * create 3 sheets ; * Humpty Dumpty fell off the wall; %utlfkil(d:/xls/origin.xlsx); libname xls "d:/xls/origin.xlsx"; data xls._asia xls._usa xls._europe; set sashelp.cars; select (origin); when ( 'Asia' ) output xls._Asia ; when ( 'Europe') output xls._Europe ; when ( 'USA' ) output xls._USA ; /* leave off otherwise to force an error */ end; ;run;quit; /* NOTE: There were 428 observations read from the data set SASHELP.CARS. NOTE: The data set XLS._asia has 158 observations and 15 variables. NOTE: The data set XLS._usa has 147 observations and 15 variables. NOTE: The data set XLS._europe has 123 observations and 15 variables. */ * Humpty Dumpty was put back together again; data cars; set xls._:; run;quit; /* NOTE: There were 158 observations read from the data set XLS.'_asia$'n. NOTE: There were 123 observations read from the data set XLS.'_europe$'n. NOTE: There were 147 observations read from the data set XLS.'_usa$'n. NOTE: The data set WORK.CARS has 428 observations and 15 variables. */ proc means data=cars median; ;run;quit; /* The MEANS Procedure Variable Label Median ------------------------------------------ MSRP MSRP 27635.00 INVOICE INVOICE 25294.50 ENGINESIZE ENGINESIZE 3.0000000 CYLINDERS CYLINDERS 6.0000000 HORSEPOWER HORSEPOWER 210.0000000 MPG_CITY MPG_CITY 19.0000000 MPG_HIGHWAY MPG_HIGHWAY 26.0000000 WEIGHT WEIGHT 3474.50 WHEELBASE WHEELBASE 107.0000000 LENGTH LENGTH 187.0000000 ------------------------------------------ */ ... View more

This code should help you with initial cleanup. Just put the location of pythin in the utl_submit_p64 get python https://www.python.org/downloads/ You do not need to know much Python to exceute the USADDRESS package. There are excellent eduacational materials on the web. libname adr "d:/adr"; options fmtsearch=(adr.adr_formats_v1 work.formats); *************************************************************************; data adr.&pgm._unitabv; retain fmtname "$adr_unit2abv"; informat start $12. label $5.; input start label; end=start; cards4; APARTMENT APT BASEMENT BSMT BUILDING BLDG DEPARTMENT DEPT FLOOR FL FRONT FRNT HANGAR HNGR LOBBY LBBY LOT LOT LOWER LOWR OFFICE OFC PENTHOUSE PH PIER PIER REAR REAR ROOM RM SIDE SIDE SLIP SLIP SPACE SPC STOP STOP SUITE STE TRAILER TRLR UNIT UNIT UPPER UPPR ;;;; ;run;quit; data adr.&pgm._abv2stdes; retain fmtname "$adr_abv2stdes"; informat start $7. label $16.; input start label; end=start; cards4; ALY ALLEY ANX ANNEX APT APARTMENT ARC ARCADE AVE AVENUE BSMT BASEMENT BYU BAYOU BCH BEACH BND BEND BLF BLUFF BTM BOTTOM BLVD BOULEVARD BR BRANCH BRG BRIDGE BRK BROOK BLDG BUILDING BG BURG BYP BYPASS CP CAMP CYN CANYON CPE CAPE CSWY CAUSEWAY CTR CENTER CIR CIRCLE CLF CLIFF CLFS CLIFFS CLB CLUB COR CORNER CORS CORNERS CRSE COURSE CT COURT CTS COURTS CV COVE CRK CREEK CRES CRESCENT XING CROSSING DL DALE DM DAM DEPT DEPARTMENT DV DIVIDE DR DRIVE EST ESTATE EXPY EXPRESSWAY EXT EXTENSION FLS FALLS FRY FERRY FLD FIELD FLDS FIELDS FLT FLAT FL FLOOR FRD FORD FRST FOREST FRG FORGE FRK FORK FRKS FORKS FT FORT FWY FREEWAY FRNT FRONT GDN GARDEN GDNS GARDENS GTWY GATEWAY GLN GLEN GRN GREEN GRV GROVE HNGR HANGER HBR HARBOR HVN HAVEN HTS HEIGHTS HWY HIGHWAY HL HILL HLS HILLS HOLW HOLLOW INLT INLET IS ISLAND ISS ISLANDS JCT JUNCTION KY KEY KNL KNOLL KNLS KNOLLS LK LAKE LKS LAKES LNDG LANDING LN LANE LGT LIGHT LF LOAF LBBY LOBBY LCK LOCK LCKS LOCKS LDG LODGE LOWR LOWER MNR MANOR MDW MEADOW MDWS MEADOWS ML MILL MLS MILLS MSN MISSION MT MOUNT MTN MOUNTAIN NCK NECK OFC OFFICE ORCH ORCHARD PKWY PARKWAY PH PENTHOUSE PNE PINE PNES PINES PL PLACE PLN PLAIN PLNS PLAINS PLZ PLAZA PT POINT PRT PORT PR PRAIRIE RADL RADIAL RNCH RANCH RPD RAPID RPDS RAPIDS RST REST RDG RIDGE RIV RIVER RD ROAD RM ROOM SHL SHOAL SHLS SHOALS SHR SHORE SHRS SHORES SPC SPACE SPG SPRING SPGS SPRINGS SQ SQUARE STA STATION STRA STRAVENUE STRM STREAM ST STREET STE SUITE SMT SUMMIT TER TERRACE TRCE TRACE TRAK TRACK TRFY TRAFFICWAY TRL TRAIL TRLR TRAILER TUNL TUNNEL TPKE TURNPIKE UN UNION UPPR UPPER VLY VALLEY VIA VIADUCT VW VIEW VLG VILLAGE VL VILLE VIS VISTA WAY WAY WL WELL WLS WELLS ;;;; ;run;quit; proc format cntlin=adr.&pgm._abv2stdes lib=adr.adr_formats_v1; run;quit; data adr.&pgm._nrmst; input; _infile_=upcase(_infile_); fro=scan(_infile_,1,' '); too=scan(_infile_,2,' '); if substr(strip(fro),-1,1)='S' and substr(strip(too),-1,1)='S' then too=substr(too,1,length(too)-1); cards4; ALLEY ALY ANEX ANX ANNEX ANX ARCADE ARC BEACH BCH BEND BND BLUF BLF BOTTOM BTM BRANCH BR BRIDGE BRG BRNCH BR BROOK BRK BROOKS BRKS BURG BG BURGS BGS BYPA BYP BYPAS BYP BYPASS BYP BYPS BYP CAMP CP CANYN CYN CANYON CYN CAPE CPE CAUSEWAY CSWY CAUSWAY CSWY CEN CTR CENT CTR CENTER CTR CENTERS CTRS CENTR CTR CENTRE CTR CIRC CIR CIRCL CIR CIRCLE CIR CIRCLES CIRS CK CRK CLIFF CLF CLIFFS CLFS CLUB CLB CMP CP CNTER CTR CNTR CTR CNYN CYN COMMON CMN CORNER COR CORNERS CORS COURSE CRSE COURT CT COURTS CTS COVE CV COVES CVS CR CRK CRCL CIR CRCLE CIR CRECENT CRES CREEK CRK CRESCENT CRES CRESENT CRES CREST CRST CROSSING XING CROSSROAD XRD CRSCNT CRES CRSENT CRES CRSNT CRES CRSSING XING CRSSNG XING CRT CT CURVE CURV DALE DL DAM DM DIV DV DIVIDE DV DRIV DR DRIVE DR DRIVES DRS DRV DR DVD DV ESTATE EST ESTATES ESTS EXP EXPY EXPR EXPY EXPRESS EXPY EXPRESSWAY EXPY EXPW EXPY EXTENSION EXT EXTENSIONS EXTS EXTN EXT EXTNSN EXT FALLS FLS FERRY FRY FIELD FLD FIELDS FLDS FLAT FLT FLATS FLTS FORD FRD FORDS FRDS FOREST FRST FORESTS FRST FORG FRG FORGE FRG FORGES FRGS FORK FRK FORKS FRKS FORT FT FREEWAY FWY FREEWY FWY FRRY FRY FRT FT FRWAY FWY FRWY FWY GARDEN GDN GARDENS GDNS GARDN GDN GATEWAY GTWY GATEWY GTWY GATWAY GTWY GLEN GLN GLENS GLNS GRDEN GDN GRDN GDN GRDNS GDNS GREEN GRN GREENS GRNS GROV GRV GROVE GRV GROVES GRVS GTWAY GTWY HARB HBR HARBOR HBR HARBORS HBRS HARBR HBR HAVEN HVN HAVN HVN HEIGHT HTS HEIGHTS HTS HGTS HTS HIGHWAY HWY HIGHWY HWY HILL HL HILLS HLS HIWAY HWY HIWY HWY HLLW HOLW HOLLOW HOLW HOLLOWS HOLW HOLWS HOLW HRBOR HBR HT HTS HWAY HWY INLET INLT ISLAND IS ISLANDS ISS ISLES ISLE ISLND IS ISLNDS ISS JCTION JCT JCTN JCT JCTNS JCTS JUNCTION JCT JUNCTIONS JCTS JUNCTN JCT JUNCTON JCT KEY KY KEYS KYS KNOL KNL KNOLL KNL KNOLLS KNLS LA LN LAKE LK LAKES LKS LANDING LNDG LANE LN LANES LN LDGE LDG LIGHT LGT LIGHTS LGTS LNDNG LNDG LOAF LF LOCK LCK LOCKS LCKS LODG LDG LODGE LDG LOOPS LOOP MANOR MNR MANORS MNRS MEADOW MDW MEADOWS MDWS MEDOWS MDWS MILL ML MILLS MLS MISSION MSN MISSN MSN MNT MT MNTAIN MTN MNTN MTN MNTNS MTNS MOTORWAY MTWY MOUNT MT MOUNTAIN MTN MOUNTAINS MTNS MOUNTIN MTN MSSN MSN MTIN MTN NECK NCK ORCHARD ORCH ORCHRD ORCH OVERPASS OPAS OVL OVAL PARKS PARK PARKWAY PKWY PARKWAYS PKWY PARKWY PKWY PASSAGE PSGE PATHS PATH PIKES PIKE PINE PNE PINES PNES PK PARK PKWAY PKWY PKWYS PKWY PKY PKWY PLACE PL PLAIN PLN PLAINES PLNS PLAINS PLNS PLAZA PLZ PLZA PLZ POINT PT POINTS PTS PORT PRT PORTS PRTS PRAIRIE PR PRARIE PR PRK PARK PRR PR RAD RADL RADIAL RADL RADIEL RADL RANCH RNCH RANCHES RNCH RAPID RPD RAPIDS RPDS RDGE RDG REST RST RIDGE RDG RIDGES RDGS RIVER RIV RIVR RIV RNCHS RNCH ROAD RD ROADS RDS ROUTE RTE RVR RIV SHOAL SHL SHOALS SHLS SHOAR SHR SHOARS SHRS SHORE SHR SHORES SHRS SKYWAY SKWY SPNG SPG SPNGS SPGS SPRING SPG SPRINGS SPGS SPRNG SPG SPRNGS SPGS SPURS SPUR SQR SQ SQRE SQ SQRS SQS SQU SQ SQUARE SQ SQUARES SQS STATION STA STATN STA STN STA STR ST STRAV STRA STRAVE STRA STRAVEN STRA STRAVENUE STRA STRAVN STRA STREAM STRM STREET ST STREETS STS STREME STRM STRT ST STRVN STRA STRVNUE STRA SUMIT SMT SUMITT SMT SUMMIT SMT TERR TER TERRACE TER THROUGHWAY TRWY TPK TPKE TR TRL TRACE TRCE TRACES TRCE TRACK TRAK TRACKS TRAK TRAFFICWAY TRFY TRAIL TRL TRAILS TRL TRK TRAK TRKS TRAK TRLS TRL TRNPK TPKE TRPK TPKE TUNEL TUNL TUNLS TUNL TUNNEL TUNL TUNNELS TUNL TUNNL TUNL TURNPIKE TPKE TURNPK TPKE UNDERPASS UPAS UNION UN UNIONS UNS VALLEY VLY VALLEYS VLYS VALLY VLY VDCT VIA VIADCT VIA VIADUCT VIA VIEW VW VIEWS VWS VILL VLG VILLAG VLG VILLAGE VLG VILLAGES VLGS VILLE VL VILLG VLG VILLIAGE VLG VIST VIS VISTA VIS VLLY VLY VST VIS VSTA VIS WALKS WALK WELL WL WELLS WLS WY WAY ;;;; ;run;quit; data adr.&pgm._fmtmkesfx; retain fmtname '$adr_st2sfx'; set adr.&pgm._nrmst; start=fro; end=start; label=too; ;run;quit; proc format cntlin=adr.&pgm._fmtmkesfx lib=adr.adr_formats_v1; ;run;quit; proc format cntlin=adr.&pgm._unitabv lib=adr.adr_formats_v1; ;run;quit; proc format cntlin=adr.&pgm._abv2stdes lib=adr.adr_formats_v1; ;run;quit; proc catalog cat=adr.adr_formats_v1; modify ADR_ABV2STDES.formatc(description="Reverse street short form to long form TPKE->Turnpike"); modify ADR_UNIT2ABV.formatc(description="Occupancy Type ie APARTMENT -> APT"); modify ADR_ST2SFX.formatc(description="Street suffix ie TURNPIKE -> TPKE"); ;run;quit; proc catalog cat=adr.adr_formats_v1; contents; ;run;quit; ... View more

  Suppose you have a single address string HAVE ENROLLMENT GROUP 39 E COCONUT ST 3RD FL WANT [ (u'ENROLLMENT', 'Recipient'), (u'GROUP', 'Recipient'), (u'39', 'AddressNumber'), (u'E', 'StreetNamePreDirectional'), (u'COCONUT', 'StreetName'), (u'ST', 'StreetNamePostType'), (u'3RD', 'OccupancyIdentifier'), (u'FL\n', 'OccupancyType') ] * the Python USADDRESS package can do much more than this. But lets see if it can parse the 6 permutations of the same address below. I have already used PERL to do some translations , ie East to E, Street to ST, THIRD to 3rd floor to FL. * create input for Python; data adr_badadr; file "d:/txt/KeyBadAdr.txt"; length keybadadr $100.; input; key=put(10000000000+_n_,z11.); KeyBadAdr=cats(key,_infile_); putlog KeyBadAdr; put KeyBadAdr; cards4; ENROLLMENT GROUP 39 E COCONUT ST 3RD FL ENROLLMENT GROUP 3RD FL 39 E COCONUT ST 39 E COCONUT ST ENROLLMENT GROUP 3RD FL 39 E COCONUT ST 3RD FL ENROLLMENT GROUP 3RD FL ENROLLMENT GROUP 39 E COCONUT ST 3RD FL 39 E COCONUT ST ENROLLMENT GROUP ;;;; ;run;quit; /* in the log 10000000001ENROLLMENT GROUP 39 E COCONUT ST 3RD FL 10000000002ENROLLMENT GROUP 3RD FL 39 E COCONUT ST 1000000000339 E COCONUT ST ENROLLMENT GROUP 3RD FL 1000000000439 E COCONUT ST 3RD FL ENROLLMENT GROUP 100000000053RD FL ENROLLMENT GROUP 39 E COCONUT ST 100000000063RD FL 39 E COCONUT ST ENROLLMENT GROUP */ * It looks like you will have to do a lot of manual cleaning before you can use open source packages to clean stadardize your addresses. * the most critical address parts for a strong matchcode are street number street name Note it is not really possible to know if 'Enrollment' is a recipient or placename Group could be a state or locality like UTAH, depending on its position. Also it is not always possible to get the occupancy parts. It depends on the location of the occupancy parts. You can really mess up a mailing with assumptions on non street parts. I am going to build a matchcode based on First character of first name, last name, street number, street name and zipcode. Python USADDRESS does an excellent job of parsing the the key elements for a match code, ie Street Number and Street Name. It also handles components like PO BOX. * here is the simple python code that takes your input and creates the output above. %utl_submit_py64(%nrbquote( fo = open('d:/txt/KeyBadAdrFix.txt', 'w'); import usaddress; for line in open('d:/txt/KeyBadAdr.txt'):; . addr=line[11:]; . key=line[0:11]; . res=key + str(usaddress.parse(addr)) + '\n'; . lyn=str(addr); . fo.write(str(res)); )); * good way to look the python output, you need full SAS for this. proc fslist file="d:/txt/KeyBadAdrFix.txt"; run;quit; * here is the parsing of the first address by Python 10000000001[ (u'ENROLLMENT', 'Recipient'), (u'GROUP', 'Recipient'), (u'39', 'AddressNumber'), (u'E', 'StreetNamePreDirectional'), (u'COCONUT', 'StreetName'), (u'ST', 'StreetNamePostType'), (u'3RD', 'OccupancyIdentifier'), (u'FL\n', 'OccupancyType')] Here is the last record 10000000006[ (u'3RD', 'OccupancyIdentifier'), (u'FL', 'OccupancyType'), (u'39', 'AddressNumber'), (u'E', 'StreetNamePreDirectional'), (u'COCONUT', 'StreetName'), (u'ST', 'StreetNamePostType'), (u'ENROLLMENT', 'PlaceName'), (u'GROUP\n', 'StateName') * lets see if all of these addressses have the same street number street name * create name value pairs for further processing; data KeyBadAdrNrm; retain seq 0 adrrec name value; keep seq adrrec name value; length txt $100 key $11 str $700; infile "d:/txt/KeyBadAdrFix.txt" lrecl=1034; input; adrrec=substr(_infile_,1,11); parens=countc(_infile_,')'); str=substr(_infile_,12); do i=1 to parens; txt=substr(scan(str,i,')'),3); txt=compress(txt,"'[()'"); txt=substr(prxchange('s/\\n//',2,txt),2); seq=seq+1; value=scan(txt,1,','); name=scan(txt,2,','); output; end; run;quit; p to 40 obs WORK.KEYBADADRNRM total obs=48 ENROLLMENT GROUP 39 E COCONUT ST 3RD FL bs SEQ ADRREC NAME VALUE 1 1 10000000001 Recipient ENROLLMENT 2 2 10000000001 Recipient GROUP 3 3 10000000001 AddressNumber 39 4 4 10000000001 StreetNamePreDirectional E 5 5 10000000001 StreetName COCONUT 6 6 10000000001 StreetNamePostType ST 7 7 10000000001 OccupancyIdentifier 3RD 8 8 10000000001 OccupancyType FL 9 9 10000000002 Recipient ENROLLMENT 10 10 10000000002 Recipient GROUP 11 11 10000000002 Recipient 3RD 12 12 10000000002 Recipient FL 13 13 10000000002 AddressNumber 39 14 14 10000000002 StreetNamePreDirectional E 15 15 10000000002 StreetName COCONUT 16 16 10000000002 StreetNamePostType ST If you check you will see all addresses had street number and street name parsed correctly. Other elements depend on position in string, no way around this. * you need to upcase, remove punctuation and convert to standard USPS abreviations. Perl has some free translations. CAUSEWAY CSWY CAUSWAY CSWY CASWAY CSWY CEN CTR CENT CTR CENTER CTR CENTERS CTRS CENTR CTR CENTRE CTR CIRC CIR CIRCL CIR CIRCLE CIR CIRCLES CIRS ; * invoking python from SAS; %macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands"; * write the program to a temporary file; filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v; data _null_; length pgm $32755 cmd $255; file py_pgm ; pgm="&pgm"; semi=countc(pgm,';'); do idx=1 to semi; cmd=cats(scan(pgm,idx,';')); if cmd=:'.' then cmd=substr(cmd,2); put cmd $char96.; putlog cmd $char96.; end; run; %let _loc=%sysfunc(pathname(py_pgm)); %put &_loc; filename rut pipe "C:\Python_27_64bit/python.exe &_loc"; data _null_; file print; infile rut; input; put _infile_; run; filename rut clear; filename py_pgm clear; %mend utl_submit_py64;   ... View more

Suppose you have a single address string HAVE ENROLLMENT GROUP 39 E COCONUT ST 3RD FL WANT [ (u'ENROLLMENT', 'Recipient'), (u'GROUP', 'Recipient'), (u'39', 'AddressNumber'), (u'E', 'StreetNamePreDirectional'), (u'COCONUT', 'StreetName'), (u'ST', 'StreetNamePostType'), (u'3RD', 'OccupancyIdentifier'), (u'FL\n', 'OccupancyType') ] * the Python USADDRESS package can do much more than this. But lets see if it can parse the 6 permutations of the same address below. I have already used PERL to do some translations , ie East to E, Street to ST, THIRD to 3rd floor to FL. * create input for Python; data adr_badadr; file "d:/txt/KeyBadAdr.txt"; length keybadadr $100.; input; key=put(10000000000+_n_,z11.); KeyBadAdr=cats(key,_infile_); putlog KeyBadAdr; put KeyBadAdr; cards4; ENROLLMENT GROUP 39 E COCONUT ST 3RD FL ENROLLMENT GROUP 3RD FL 39 E COCONUT ST 39 E COCONUT ST ENROLLMENT GROUP 3RD FL 39 E COCONUT ST 3RD FL ENROLLMENT GROUP 3RD FL ENROLLMENT GROUP 39 E COCONUT ST 3RD FL 39 E COCONUT ST ENROLLMENT GROUP ;;;; ;run;quit; /* in the log 10000000001ENROLLMENT GROUP 39 E COCONUT ST 3RD FL 10000000002ENROLLMENT GROUP 3RD FL 39 E COCONUT ST 1000000000339 E COCONUT ST ENROLLMENT GROUP 3RD FL 1000000000439 E COCONUT ST 3RD FL ENROLLMENT GROUP 100000000053RD FL ENROLLMENT GROUP 39 E COCONUT ST 100000000063RD FL 39 E COCONUT ST ENROLLMENT GROUP */ * It looks like you will have to do a lot of manual cleaning before you can use open source packages to clean stadardize your addresses. * the most critical address parts for a strong matchcode are street number street name Note it is not really possible to know if 'Enrollment' is a recipient or placename Group could be a state or locality like UTAH, depending on its position. Also it is not always possible to get the occupancy parts. It depends on the location of the occupancy parts. You can really mess up a mailing with assumptions on non street parts. I am going to build a matchcode based on First character of first name, last name, street number, street name and zipcode. Python USADDRESS does an excellent job of parsing the the key elements for a match code, ie Street Number and Street Name. It also handles components like PO BOX. * here is the simple python code that takes your input and creates the output above. %utl_submit_py64(%nrbquote( fo = open('d:/txt/KeyBadAdrFix.txt', 'w'); import usaddress; for line in open('d:/txt/KeyBadAdr.txt'):; . addr=line[11:]; . key=line[0:11]; . res=key + str(usaddress.parse(addr)) + '\n'; . lyn=str(addr); . fo.write(str(res)); )); * good way to look the python output, you need full SAS for this. proc fslist file="d:/txt/KeyBadAdrFix.txt"; run;quit; * here is the parsing of the first address by Python 10000000001[ (u'ENROLLMENT', 'Recipient'), (u'GROUP', 'Recipient'), (u'39', 'AddressNumber'), (u'E', 'StreetNamePreDirectional'), (u'COCONUT', 'StreetName'), (u'ST', 'StreetNamePostType'), (u'3RD', 'OccupancyIdentifier'), (u'FL\n', 'OccupancyType')] Here is the last record 10000000006[ (u'3RD', 'OccupancyIdentifier'), (u'FL', 'OccupancyType'), (u'39', 'AddressNumber'), (u'E', 'StreetNamePreDirectional'), (u'COCONUT', 'StreetName'), (u'ST', 'StreetNamePostType'), (u'ENROLLMENT', 'PlaceName'), (u'GROUP\n', 'StateName') * lets see if all of these addressses have the same street number street name * create name value pairs for further processing; data KeyBadAdrNrm; retain seq 0 adrrec name value; keep seq adrrec name value; length txt $100 key $11 str $700; infile "d:/txt/KeyBadAdrFix.txt" lrecl=1034; input; adrrec=substr(_infile_,1,11); parens=countc(_infile_,')'); str=substr(_infile_,12); do i=1 to parens; txt=substr(scan(str,i,')'),3); txt=compress(txt,"'[()'"); txt=substr(prxchange('s/\\n//',2,txt),2); seq=seq+1; value=scan(txt,1,','); name=scan(txt,2,','); output; end; run;quit; p to 40 obs WORK.KEYBADADRNRM total obs=48 ENROLLMENT GROUP 39 E COCONUT ST 3RD FL bs SEQ ADRREC NAME VALUE 1 1 10000000001 Recipient ENROLLMENT 2 2 10000000001 Recipient GROUP 3 3 10000000001 AddressNumber 39 4 4 10000000001 StreetNamePreDirectional E 5 5 10000000001 StreetName COCONUT 6 6 10000000001 StreetNamePostType ST 7 7 10000000001 OccupancyIdentifier 3RD 8 8 10000000001 OccupancyType FL 9 9 10000000002 Recipient ENROLLMENT 10 10 10000000002 Recipient GROUP 11 11 10000000002 Recipient 3RD 12 12 10000000002 Recipient FL 13 13 10000000002 AddressNumber 39 14 14 10000000002 StreetNamePreDirectional E 15 15 10000000002 StreetName COCONUT 16 16 10000000002 StreetNamePostType ST If you check you will see all addresses had street number and street name parsed correctly. Other elements depend on position in string, no way around this. * you need to upcase, remove punctuation and convert to standard USPS abreviations. Perl has some free translations. CAUSEWAY CSWY CAUSWAY CSWY CASWAY CSWY CEN CTR CENT CTR CENTER CTR CENTERS CTRS CENTR CTR CENTRE CTR CIRC CIR CIRCL CIR CIRCLE CIR CIRCLES CIRS ; * invoking python from SAS; %macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands"; * write the program to a temporary file; filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v; data _null_; length pgm $32755 cmd $255; file py_pgm ; pgm="&pgm"; semi=countc(pgm,';'); do idx=1 to semi; cmd=cats(scan(pgm,idx,';')); if cmd=:'.' then cmd=substr(cmd,2); put cmd $char96.; putlog cmd $char96.; end; run; %let _loc=%sysfunc(pathname(py_pgm)); %put &_loc; filename rut pipe "C:\Python_27_64bit/python.exe &_loc"; data _null_; file print; infile rut; input; put _infile_; run; filename rut clear; filename py_pgm clear; %mend utl_submit_py64; ... View more