You might want to take a look at https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;326f83bd.1612c fro a related solution. The code can run as is in IML ... View more

  SAS Forum: Copying all datasets in sashelp.class that have a specified prefix to work directory but stopping if child process has an error This is a dumb example but demonstrates the flexibility of DOSUBL. This algoritm has other applications beyond this dumb example; Replacing 'call execute'? inspired by https://goo.gl/HrHBcV https://communities.sas.com/t5/Base-SAS-Programming/Using-a-macro-to-loop-through-files-and-process-data-and-output/m-p/318992#M69958 HAVE ==== %let mem=MAC; data have; set sashelp.vtable (keep=libname memname where=(libname="SASHELP" and memname=:"&MEM")); run;quit; These datasets in sashelp (then begin with MAC) LIBNAME MEMNAME 1 SASHELP MACRS10 2 SASHELP MACRS15 3 SASHELP MACRS20 4 SASHELP MACRS3 5 SASHELP MACRS5 6 SASHELP MACRS7 WANT (want the process of copying to stop if any dataset has 0 obs) ==== LIBNAME MEMNAME WORK WANT_MACRS10 * copy of sashelp.MACRS10 in work directory WORK WANT_MACRS15 WORK WANT_MACRS20 WORK WANT_MACRS3 WORK WANT_MACRS5 WORK WANT_MACRS7 WORKING CODE rc=dosubl('....' return number of obs loaded into obss_address FULL SIMPLE SOLUTION ==================== If SAS has fixed the 2013 reported issue around sending macro variables from the child to the parent datastep, then you can use the simple solution. Otherwise you will have to use the more complex one. I am running an 9.4M2 hopefully it is fixed in later versions? The advantage of dosubl is two way communication with a child process. You can trap errors more easily and not even generate additional code. The simple one %symdel obs_copied ; /* just incase */ data _null_; set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC")); call symputx('memname',memname); /* pass to child */ rc=dosubl(' proc sql; create table WANT_&memname as select * from sashelp.&memname ;quit; %let obs_copied=&sqlobs; '); if "&obs_copied" = "0" then stop; /* from child */ run;quit; FULL COMPLEX SOLUTION ===================== %symdel err obs_address ; data _null_; retain obs_address 0; set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC")); call symputx('memname',memname); call symputx("obs_address",put(addrlong(obs_address),hex16.)); rc=dosubl(' proc sql; create table WANT_&memname as select * from sashelp.&memname ;quit; data _null_; call pokelong(&sqlobs,"&obs_address"x,8); run;quit; '); put memname obs_address=; if obs_address = 0 then stop; run;quit; NOTE: Table WORK.WANT_MACRS7 created, with 8 rows and 2 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:41 AM Step Count 430 Switch Count 0 SYMBOLGEN: Macro variable SQLOBS resolves to 8 SYMBOLGEN: Macro variable OBS_ADDRESS resolves to D8E18A0A00000000 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 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:42 AM Step Count 430 Switch Count 0 MACRS7 OBS_ADDRESS=8 NOTE: There were 6 observations read from the data set SASHELP.VTABLE. WHERE (libname='SASHELP') and (memname=:'MAC'); NOTE: DATA statement used (Total process time): real time 1.64 seconds user cpu time 0.43 seconds system cpu time 0.90 seconds memory 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:42 AM Step Count 430 Switch Count 12 2905! quit;   ... View more

You can call a macro in DOSUBL ... View more

SAS Forum: Copying all datasets in sashelp.class that have a specified prefix to work directory but stopping if child process has an error This is a dumb example but demonstrates the flexibility of DOSUBL. This algoritm has other applications beyond this dumb example; Replacing 'call execute'? inspired by https://goo.gl/HrHBcV https://communities.sas.com/t5/Base-SAS-Programming/Using-a-macro-to-loop-through-files-and-process-data-and-output/m-p/318992#M69958 HAVE ==== %let mem=MAC; data have; set sashelp.vtable (keep=libname memname where=(libname="SASHELP" and memname=:"&MEM")); run;quit; These datasets in sashelp (then begin with MAC) LIBNAME MEMNAME 1 SASHELP MACRS10 2 SASHELP MACRS15 3 SASHELP MACRS20 4 SASHELP MACRS3 5 SASHELP MACRS5 6 SASHELP MACRS7 WANT (want the process of copying to stop if any dataset has 0 obs) ==== LIBNAME MEMNAME WORK WANT_MACRS10 * copy of sashelp.MACRS10 in work directory WORK WANT_MACRS15 WORK WANT_MACRS20 WORK WANT_MACRS3 WORK WANT_MACRS5 WORK WANT_MACRS7 WORKING CODE rc=dosubl('....' return number of obs loaded into obss_address FULL SIMPLE SOLUTION ==================== If SAS has fixed the 2013 reported issue around sending macro variables from the child to the parent datastep, then you can use the simple solution. Otherwise you will have to use the more complex one. I am running an 9.4M2 hopefully it is fixed in later versions? The advantage of dosubl is two way communication with a child process. You can trap errors more easily and not even generate additional code. The simple one %symdel obs_copied ; /* just incase */ data _null_; set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC")); call symputx('memname',memname); /* pass to child */ rc=dosubl(' proc sql; create table WANT_&memname as select * from sashelp.&memname ;quit; %let obs_copied=&sqlobs; '); if "&obs_copied" = "0" then stop; /* from child */ run;quit; FULL COMPLEX SOLUTION ===================== %symdel err obs_address ; data _null_; retain obs_address 0; set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC")); call symputx('memname',memname); call symputx("obs_address",put(addrlong(obs_address),hex16.)); rc=dosubl(' proc sql; create table WANT_&memname as select * from sashelp.&memname ;quit; data _null_; call pokelong(&sqlobs,"&obs_address"x,8); run;quit; '); put memname obs_address=; if obs_address = 0 then stop; run;quit; NOTE: Table WORK.WANT_MACRS7 created, with 8 rows and 2 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:41 AM Step Count 430 Switch Count 0 SYMBOLGEN: Macro variable SQLOBS resolves to 8 SYMBOLGEN: Macro variable OBS_ADDRESS resolves to D8E18A0A00000000 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 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:42 AM Step Count 430 Switch Count 0 MACRS7 OBS_ADDRESS=8 NOTE: There were 6 observations read from the data set SASHELP.VTABLE. WHERE (libname='SASHELP') and (memname=:'MAC'); NOTE: DATA statement used (Total process time): real time 1.64 seconds user cpu time 0.43 seconds system cpu time 0.90 seconds memory 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:42 AM Step Count 430 Switch Count 12 2905! quit; ... View more

  You might want to take a look at    https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;36aeae18.1612c ... View more

The advantage of dosubl is two way communication with a child process. %symdel err obs_address ; data _null_; retain obs_address 0; set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC")); call symputx('memname',memname); call symputx("obs_address",put(addrlong(obs_address),hex16.)); rc=dosubl(' proc sql; create table WANT_&memname as select * from sashelp.&memname ;quit; data _null_; call pokelong(&sqlobs,"&obs_address"x,8); run;quit; '); put memname obs_address=; if obs_address = 0 then stop; run;quit; NOTE: Table WORK.WANT_MACRS7 created, with 8 rows and 2 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:41 AM Step Count 430 Switch Count 0 SYMBOLGEN: Macro variable SQLOBS resolves to 8 SYMBOLGEN: Macro variable OBS_ADDRESS resolves to D8E18A0A00000000 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 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:42 AM Step Count 430 Switch Count 0 MACRS7 OBS_ADDRESS=8 NOTE: There were 6 observations read from the data set SASHELP.VTABLE. WHERE (libname='SASHELP') and (memname=:'MAC'); NOTE: DATA statement used (Total process time): real time 1.64 seconds user cpu time 0.43 seconds system cpu time 0.90 seconds memory 7453.53k OS Memory 27300.00k Timestamp 12/14/2016 08:52:42 AM Step Count 430 Switch Count 12 2905! quit; ... View more

As a side note I always use the combination run;quit; for two reasons It always works and will give an error for run group processing , which I can then fix. 1. If I highlight a datastep and RMB then I am assured it will run. 2. I am getting old and do not want to clutter my mind with 'less; useful commands. I also always use 'cards4' and ';;;;' because it seems to always work and again I am getting old and rather not clutter my mind. ... View more

I put the following magic string on function 12. All I have to do is hit F12, highlight and hit RMB. Takes about 1 second for me to fix the issue. Works best in the more powerfull old text editor?, not EE, EG or SAS Studio ;;;;/*'*/ *);*};*];*/;/*"*/;%mend;run;quit;%end;end;run;endcomp;%utlfix; %macro utlfix(dum); * fix frozen sas and restore to invocation ; dm "odsresults;clear;"; ods results off; options ls=171 ps=65;run;quit; ods listing; ods select all; ods excel close; ods graphics off; proc printto;run; goptions reset=all; endsubmit; endrsubmit; run;quit; %utlopts; %mend utlfix; ... View more

This does not yield the records you want but might be useful for debugging. You may be able to alter it to get records. Venn diagram analysis of the non unique identifiers in two datasets Up to 5 dimensional ven diagrams are availiable along with Ian Whitlocks sets of overlaps algorithm. May only work with relatively small datasets (less that a million identifiers) inspired by https://goo.gl/XC0SKq https://communities.sas.com/t5/SAS-Data-Management/Creating-subset-of-data-by-matching-observations-to-second-data/m-p/318359 HAVE two datasets with a common but non unique identifiers ====================================================== Up to 40 obs WORK.SET1 total obs=84,151 EPISODE_ Obs NR 1 250000 2 250003 3 250006 4 250009 5 250012 6 250012 .... Up to 40 obs WORK.SET2 total obs=200,818 EPISODE_ Obs NR 1 1000 2 1005 3 1005 4 1015 5 1020 6 1025 7 1030 8 1035 .... WANT the venn diagram showing number of Set A is Element=EPISODE_NR Set B is Element=EPISODE_NR A Union B 284,969 Total 266,468 Distincts SET1 SET2 84,151 200,818 Totals 83,334 199,801 Distincts *8888888888* *88888888* 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 Distincts 8 Distincts 8 Distincts 8 8 66,667 8 16,667 8 183,134 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 *8888888888* *8888888888* %macro utl_ven2 ( utitle=Venn diagram for two tables, uinmem1=work.EpoNspMqs, /* first input SAS table */ uinmem2=work.EpoNspM_f, /* second input SAS table */ uvar1=qprikey, /* field names do not have to be the same */ uvar2=mprikey ) / des="VENN diagram two datasets"; %put %sysfunc(ifc(%sysevalf(%superq(uinmem1)=,boolean),**** Please Provide 1st SAS dataset ****,)); %put %sysfunc(ifc(%sysevalf(%superq(uinmem2)=,boolean),**** Please Provide 2nd SAS dataset ****,)); %put %sysfunc(ifc(%sysevalf(%superq(uvar1)=,boolean),**** Please Provide 1st Keys ****,)); %put %sysfunc(ifc(%sysevalf(%superq(uvar2)=,boolean),**** Please Provide 2nd Keys ****,)); %if %eval( %sysfunc(ifc(%sysevalf(%superq(uinmem1)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uinmem2)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uvar1)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uvar2)=,boolean),1,0)) ) eq 0 %then %do; /* for testing without macro data class1;set sashelp.class;if mod(_n_,2)=0; if _n_=11 then name='Joyce';run; data class2;set sashelp.class;if mod(_n_,3)=0; if _n_=1 then name='Major';run; %let uinmem1=class1; %let uinmem2=class2; %let uvar1=name; %let uvar2=name; */ %let utl_lvlonea =%upcase(%sysfunc(ifc(%index(&uinmem1,%str(.))=0,WORK,%upcase(%scan(&uinmem1,1,%str(.)))))); %let utl_lvltwoa =%upcase(%sysfunc(ifc(%index(&uinmem1,%str(.))=0,&uinmem1,%upcase(%scan(&uinmem1,2,%str(.)))))); %let utl_lvlonetwoa= &utl_lvlone..&utl_lvltwo; %let utl_lvloneb =%upcase(%sysfunc(ifc(%index(&uinmem2,%str(.))=0,WORK,%upcase(%scan(&uinmem2,1,%str(.)))))); %let utl_lvltwob =%upcase(%sysfunc(ifc(%index(&uinmem2,%str(.))=0,&uinmem2,%upcase(%scan(&uinmem2,2,%str(.)))))); %let utl_lvlonetwob= &utl_lvlone..&utl_lvltwo; %let uinmema = %upcase( &utl_lvltwoa); %let uinmemb = %upcase( &utl_lvltwob); %let uvara = %upcase( &uvar1); %let uvarb = %upcase( &uvar2); /*----------------------------------------------*\ | SQL code to get counts | | udsta = distinct values is set a | | udstb = distinct values is set b | | | | unota = values not in a | | unotb = values not in b | | | | uaib = values in intersection | \*----------------------------------------------*/ proc sql noprint; select memlabel , nobs into :ulaba , :uina from dictionary.tables where libname="&utl_lvlonea" and memname="&uinmema"; select memlabel , nobs into :ulabb , :uinb from dictionary.tables where libname="&utl_lvloneb" and memname="&uinmemb"; select count(distinct &uvara) into :udsta from &uinmema; select count(distinct &uvarb) into :udstb from &uinmemb; select count(distinct &uvara) into :unotb from &uinmema where &uvara not in (select &uvarb as &uvara from &uinmemb); select count(distinct &uvarb) into :unota from &uinmemb where &uvarb not in (select &uvara as &uvarb from &uinmema); select count(distinct &uvara) into :uaib from &uinmema where &uvara in (select &uvarb as &uvara from &uinmemb); quit; run; %let udstab=%eval(&unota + &unotb + &uaib); /* total distinct*/ %let uaub=%eval(&uina + &uinb); /* a union b */ %put unota=&unota; /* not in a */ %put unotb=&unotb; /* not in b */ %put uaib=&uaib; /* a intersect b */ %put uina=&uina; /* in a */ %put uinb=&uinb; /* in b */ data _null_; file print; uaub=%eval(&uina + &uinb); unota=&unota; unotb=&unotb; udsta=&udsta; udstb=&udstb; udstab=&udstab; uaib=&uaib; uina=&uina; uinb=&uinb; put #02 @10 "Set A is %trim(%left(&ulaba))" @53 "Element=%trim(%left(&uvara))"; put #03 @10 "Set B is %trim(%left(&ulabb))" @53 "Element=%trim(%left(&uvarb))"; put #05 @26 " A Union B "@; put #06 @22 uaub comma12. @; put #07 @22 udstab comma12.@; put #08 @20 "&uinmema" @35 "&uinmemb"@; put #09 @16 uina comma12. @31 uinb comma12.; put #10 @16 udsta comma12. @31 udstb comma12.; put #12 @3 " *8888888888* *88888888* "@; put #13 @3 " 8 8 8 8 "@; put #14 @3 " 8 8 8 8 "@; put #15 @3 " 8 8 8 "@; put #16 @3 " 8 8 8 8 "@; put #17 @3 " 8 8 8 8 "@; put #18 @3 " 8 8 8 8 "@; put #19 @3 " 8 8 8 8 "@; put #20 @3 " 8 8 8 8 "@; put #21 @3 " 8 8 8 8 "@; put #22 @3 " 8 8 8 8 "@; put #23 @3 " 8 8 8 8 "@; put #23 @10 unotb comma12. @22 uaib comma12. @39 unota comma12. @; put #23 @3 " 8"@; put #24 @3 " 8 8 8 8 "@; put #25 @3 " 8 8 8 8 "@; put #26 @3 " 8 8 8 8 "@; put #27 @3 " 8 8 8 8 "@; put #28 @3 " 8 8 8 8 "@; put #29 @3 " 8 8 8 8 "@; put #30 @3 " 8 8 8 8 "@; put #31 @3 " 8 8 8 8 "@; put #32 @3 " 8 8 8 8 "@; put #33 @3 " 8 8 8 "@; put #34 @3 " 8 8 8 8 "@; put #35 @3 " *8888888888* *8888888888* "@; put #36 @3 " "@; stop; run; %end; %mend utl_ven2; data set1; do episode_nr = 250000 to 500000 by 3; if uniform(-1)<.01 then output; /*inject an dup error */ output; end; run;quit; data set2; do episode_nr = 1000 to 1000000 by 5; if uniform(-1)<.005 then output; /*inject an dup error */ output; end; run;quit; %utl_ven2 ( uinmem1=set1, uinmem2=set2, uvar1=episode_nr, uvar2=episode_nr ); ... View more

Importing special characters in EXCEL XML sheets and creatting a HTM, PDF or RTF report with the special characters. There is probably an easier solution but this might lead to a better solution. HAVE an EXCEL Sheet with synmbols for 'less than or equal'. When I import to SAS the symbols are gone. HAVE ==== If you view the sheet, It looks fine witht the less than or equal as one character. I had to use two lines because I like clear text. +------------------------------+ | A | +------------------------------+ 1 | Pool 15 < year < 25 | | - - | +------------------------------+ WANT an HTML, RTF or PDF that looks like the Excel file ================================ +------------------------------+ | Less than or equal | +------------------------------+ 1 | Pool 15 < year < 25 | | - - | +------------------------------+ But I get +------------------------------+ | Less than or equal | +------------------------------+ 1 | Pool 15 = year = 25 | | | +------------------------------+ WORKING CODE ============ read.xlsx("d:/xls/symbols.xlsx" ,1 ,colClasses=c("character") ,stringsAsFactors=FALSE ,encoding="UTF-16"); FULL SOLUTION ============= * You can use proc IML and submit/R to do this; %utl_submit_r64(' library(xlsx); x<-read.xlsx("d:/xls/symbols.xlsx",1,colClasses=c("character"),stringsAsFactors=FALSE,encoding="UTF-16"); str(x); x$symbols; for (i in 1:3) { write(x$symbols[i], file = "d:/txt/symbols.txt",sep="\n"); }; '); * Convert 'lesss than or equal' in UTF-16 to unicode; * also get rid of the non breaking space; data fix; length le $64; infile "d:/txt/symbols.txt"; input; * remove the non-breaking space; _infile_=tranwrd(_infile_,'C2A0C2A0'x,'33'x); _infile_=tranwrd(_infile_,'E289A4'x,'^{unicode 2264}'); put _infile_; put _infile_ $hex96.; le=_infile_; run;quit; * create html file with symbols; ods escapechar='^'; ods html file="d:/htm/le.htm"; proc report data=fix(obs=1) nowd; cols le; define le /display style=[asis=on]; run;quit; ods html close; DETAILS ======= It is difficult to provide an exact solution because I cannot see exactly what is in the excel cell in hex without yout spreadsheet.. I am going to assume your excel data is encoded at UTL-16. see http://graphemica.com/%E2%89%A4 I pasted your code into excel and then used R and UTF-16 encoding to create a text file. Note this should be possible in SAS, you should be able to set the encoding. +------------------------------+ | A | +------------------------------+ 1 | Pool   15≤ years≤ 25 | +------------------------------+ Here is what it looks like in hex 506F6F6CC2A0C2A0203135E289A4207965617273E289A4203235 Note E289A4 is UTF-16 for 'less than or equal' Non Breaking Space NBSP NBSP LE LE 506F6F6C C2A0 C2A0 203135 E289A4 207965617273 E289A4 203235 Now lets create a report in html with the le symbol HTML Entity (Named) &le; HTML Entity (Decimal) &#8804; HTML Entity (Hexadecimal) &#x2264; SAS ^{unicode 2264} ... View more