SAS Forum: How to import Excel-data without ACCESS to PC Files If you have the IML interface to R you can easily recode my solution. Python and Perl can also read sas7bdat and write to excel. inspired by https://goo.gl/HuxBtO https://communities.sas.com/t5/Base-SAS-Programming/How-to-import-Excel-data-without-ACCESS-to-PC-Files/m-p/325568 HAVE SAS DATASET ================ Up to 40 obs from sd1.class total obs=6 Obs NAME AGE 1 Alfred 14 2 Alice 13 3 Barbara 13 4 Carol 14 5 Henry 14 6 James 12 WANT (EXCEL SHEET) Sheet HAVE +-------------------------+ | | A | B | +------+------------------+ | | | | | 1 | NAME | AGE| | 2 | Alfred | 14 | | 3 | Alice | 13 | | 4 | Barbara | 13 | | 5 | Carol | 14 | | 6 | Henry | 14 | +------------------+------+ SOLUTION WORKING CODE ( CAN RECODE IN IML INTERFACE TO R) R writeWorksheet(wb,have,sheet="have"); FULL SOLUTION ============== * CREATE SOME DATA; options validvarname=upcase; libname sd1 "d:/sd1"; data sd1.class; set sashelp.class(keep=name age obs=6); run;quit; %utl_submit_r64(' library(haven); library(XLConnect); have<-read_sas("d:/sd1/class.sas7bdat"); wb <- loadWorkbook("d:/xls/classout.xlsx",create = TRUE); createSheet(wb, name="have"); writeWorksheet(wb,have,sheet="have"); saveWorkbook(wb); run;quit; '); ... View more

SAS Forum: How to Highlight Changed Values in Excel inspired by https://goo.gl/DzWy7P https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-Highlight-Changed-Values-in-Excel/m-p/324623 HAVE (TWO DATASETS OLDREP) ============================ OLDREP Up to 40 obs from oldrep 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 NEW REP Up to 40 obs from newrep total obs=19 Obs NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69 112.5 2 Barbara F 13 65.3 98 3 Bill M 13 65 82 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 14 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 WANT (WHAT CHANGED - You can use this to highligt the diference) ================================================================ REPDELTA dataset p to 40 obs from repdelta total obs=3 Obs NAME SEX AGE HEIGHT WEIGHT __SPLIT __TYPE 1 Alice F 13 56.5 84 OLD DELETE 2 Bill M 13 65 82 NEW INSERT 3 Philip M 14 72 150 NEW UPDATE WORKING CODE ============ %utl_delta ( uinmem1 =oldrep, /* Last Months Data */ uinmem2 =newrep, /* Current Month Data */ uinkey =name, /* primary unique key both tables */ uotmem1 =repdelta, /* delta tble for RDBMS update */ uotmem2= repsame /* what did no change */ ); Also a macro is given to apply the chages to the old to make the new * apply the repdelta to make old look like new; %utl_update ( master=oldrep ,transaction=repdelta ,key=name ); FULL SOLUTION ============= /* T003300 GIVEN TWO DATASETS CREATE AND APPLY THE TRANSACTION DATASET THAT WILL MAKE THE TABLES THE SAME */ * * * * ***** *** * * * ***** ** ** * * * * * * * ** ** * * * * * * * * * * * * * * * * ** **** * ***** * * **** * * * * * * * * * * * * * * * * * * * * * * * * * * * ***** ***** *** * * * * *****; /*----------------------------------------------------------*\ | Make oldrep look exactly like newrep by applying | | the delta dateset using utldmod | | utl_update is the same as macro above | | Transaction dataset must contain __type variable | | with the value UPDATE, INSERT or DELETE | \*----------------------------------------------------------*/ %macro utl_update ( master= ,transaction= ,key= ) / des="Update Insert Delete in Sybase Table"; /*-------------------------------------------------*\ | This object applyes a transaction dataset to | | a master RDBMS table or SAS dataset. The result | | is an updated master ( RDBMS data ) | \*-------------------------------------------------*/ /*----------------------------------------------*\ | IPO | | Inputs | | ====== | | transaction - Transaction table must have | | variable type which can | | have 3 values (SAS dataset) | | DELETE -- Delete Row in Master | | INSERT -- Insert Row in Master | | UPDATE -- Update Row in Master | | key - unique index | | | | Process - Modify SAS/RDBMS Table | | ====== based on transaction file | | | | Output - master Modified | | ====== | | Transaction table can be in work library | \*----------------------------------------------*/ data &master; modify &master &transaction; by &key; select (_iorc_); when (%sysrc(_dsenmr)) /* nomatch in MASTER _Add */ do; if __type="INSERT" then output &master; *put "Nomatch" __type=; _error_=0; end; when (%sysrc(_sok)) /* matched Update or Delete */ do; *put "Matched " __type=; if __type="DELETE" then remove &master; else if __type="UPDATE" then replace &master; _error_=0; end; otherwise do; put "Unexpected ERROR Condition: _IORC_ =" _IORC_; _error_=0; end; end; run; %mend utl_update; %macro utl_delta ( uinmem1 =work.oldrep, /* Last Months Data */ uinmem2 =work.newrep, /* Current Month Data */ uinkey =rep_socs, /* primary unique key both tables */ uotmem1 =repdelta, /* delta tble for RDBMS update */ uotmem2 = repsame /* records that do not change */ ) / des = "Build delta SAS table for RDBMS update"; /*----------------------------------------------*\ | WIN95 SAS611 -- UNIX SAS611 SOLARIS 2.5 | | | | | | Create a delta ( transaction ) dataset | | based on a comparison of old and new data. | | | | Use this new delta dataset to bring older | | RDBMS table up to date. ( invoke utldmod ) | | | | Proc compare new feed against updated RDBMS | | Result should be an exact comparison | | | | You are probably asking why not just drop | | the RDBMS table and load the new table. | | | | 1. As a rule we keep SAS image tables | | of most read only RDBMS tables. | | And it is very easy to do detailed analysis| | such as this before any major sales | | representative alignment. | | | | 2. The delta dataset is very useful for QC, | | before updating RDBMS tables. | | | | 3. Table may reside in as many as three | | different databases on different platforms.| | (Oracle, Sybase, Watcom, MS-Access) | | This plays to SASes strength | | | | This code represents a slightly new | | methodology and as such has not been | | completely tested. CompuCraft would | | greatly appreciate any feedback. | \*----------------------------------------------*/ /*%^&*------------------------------------------*\ | Description: | | Create a delta ( transaction ) dataset | | based on new data. | | | | This code creates the delta ( transaction ) | | dataset. | | | | IPO | | INPUTS | | ====== | | Old table ( previous month - in RDBMS ) | | (ie OLSSNJOB) | | | | SSN JOB ACTIVE | | | | 001001110 carpenter1 YES | | 002001110 plumber1 YES | | 003001110 mason1 YES | | 004001110 plumber1 YES | | 005001110 electrician1 YES | | 006001110 mason3 YES | | 008001110 mason4 NO | | | | New table ( New data not in RDBMS ) | | (ie NUSSNJOB) | | | | SSN JOB ACTIVE | | | | 001001110 carpenter1 YES | | 002001110 plumber1 YES | | 003001110 painter1 YES | | 004001110 plumber1 YES | | 005001110 electrician1 YES | | 007001110 painter2 YES | | | | PROCESS | | ======= | | Extract the old data from the RDBMS. | | | | Union old and new data. Put an indicator | | variable on union. This variable | | identifies the origin of record ( old/new). | | | | Sort the union table on all fields. | | | | If record appears only in the old table | | then deactivate the record. ( DELETE ) | | | | If a record appears only in the new data | | ( at least one field makes record diff) | | and the key appears in both new and old | | data then perform an update ( UPDATE ) | | | | If a record appears only in the new data | | and the key does not appear in old table | | then perform an insert ( INSERT ) | | | | If the record is exactly the same in | | both tables then leave it alone | | | | OUTPUT | | ====== | | Transaction data set | | | | SSN JOB ACTIVE __TYPE | | | | 006001110 mason3 YES DELETE | | 008001110 mason4 NO DELETE | | 007001110 painter2 YES INSERT | | 003001110 painter1 YES UPDATE | | | \*%^&*------------------------------------------*/ /* for testing without macro %let uinmem1 =oldrep; %let uinmem2 =newrep; %let uinkey =rep_socs; %let uotmem1 =repdelta; %let uotmem2= repsame; */ %put %sysfunc(ifc(%sysevalf(%superq(uinmem1)=,boolean), **** Please Provide Previous dataset ,)); %put %sysfunc(ifc(%sysevalf(%superq(uinmem2)=,boolean), **** Please Provide Current dataset ,)); %put %sysfunc(ifc(%sysevalf(%superq(uinkey)=,boolean),**** Please Provide primary unique key both tables ,)); %put %sysfunc(ifc(%sysevalf(%superq(uotmem1)=,boolean),**** Please Provide transaction records dataset ,)); %put %sysfunc(ifc(%sysevalf(%superq(uotmem2)=,boolean), **** Please Provide records that do not change dataset,)); %if %eval( %sysfunc(ifc(%sysevalf(%superq(uinmem1)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uinmem2)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uinkey)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uotmem1)=,boolean),1,0)) + %sysfunc(ifc(%sysevalf(%superq(uotmem2)=,boolean),1,0)) ) eq 0 %then %do; data utldlta1 ( label = "Union of current & previous month" ) / view=utldlta1; retain &uinkey; set &uinmem1 ( in = prevmnth ) &uinmem2 ( in = currmnth ); if prevmnth then __split = "OLD"; else __split = "NEW"; run; proc sql; select name into : ucols separated by ' ' from dictionary.columns where libname = %upcase('work') and memname = %upcase ( 'utldlta1' ) and name not eq %upcase( '__split' ); quit; %put ucols = &ucols; proc sort data = utldlta1 out = utldlta2; by &ucols __split; run; %let uwrds = %sysfunc(countw(&ucols)); %let ulastcol = %scan (&ucols, &uwrds ); data utldlta3 ( label = "Transactions" ) &uotmem2 ( label = "No change" ); set utldlta2; by &ucols; retain __split __type; select; /* Only in previous month */ when ( ( first.&ulastcol eq last.&ulastcol ) and __split = "OLD") do; if ( first.&uinkey eq last.&uinkey ) then do; /* unique record */ __type="DELETE"; /* key unique */ output utldlta3; /* OLD data only */ end; else do; /* unique record */ __type = "UPDATE"; /* key same */ output &uotmem2; /* other field changed */ end; end; /* Only in new month */ when ( first.&ulastcol eq last.&ulastcol ) do; /* unique record */ if ( first.&uinkey eq last.&uinkey ) then do; /* new key */ __type = "INSERT"; output utldlta3; end; else do; /* unique record */ __type = "UPDATE"; /* same key */ output utldlta3; /* new data only */ end; end; /* Duplicate records same in both months */ when ( first.&ulastcol ne last.&ulastcol ) do; /* duplicate record */ output &uotmem2; end; otherwise put "ERROR ===========> NEVER SAY NEVER <=============== ERROR"; end; run; proc sort data=utldlta3 out=&uotmem1 ( label = "Transaction dataset" index = ( &uinkey / unique ) ); by __type; run; %macro utl_nlobs(dsn); %let dsid=%sysfunc(open(&dsn));%sysfunc(attrn(&dsid,nlobs)) %let rc=%sysfunc(close(&dsid)); %mend utl_lnobs; proc print data=&uinmem1(obs=30) width=minimum noobs; title "Old RDBMS Data obs=%utl_nlobs(&uinmem1)"; run; proc print data=&uinmem2(obs=30) width=minimum noobs; title "New Feed Data obs=%utl_nlobs(&uinmem2)"; run; proc print data=&uotmem1(obs=30) width=minimum noobs; title "Transaction data set obs=%utl_nlobs(&uotmem1)"; run; proc print data=&uotmem2(obs=30 drop=__type) width=minimum noobs; title "No change data set obs=%utl_nlobs(&uotmem2)"; run; /*----------------------------------------------------------*\ | Make oldrep look exactly like newrep by applying | | the delta dateset using utldmod | \*----------------------------------------------------------*/ /* data oldrep; input rep_socs : $9. JOB : $16. ACTIVE : $3.; cards; 001001110 carpenter1 YES 002001110 plumber1 YES 003001110 mason1 YES 004001110 plumber1 YES 005001110 electrician1 YES 006001110 mason3 YES 008001110 mason4 NO ; run; data newrep; input rep_socs : $9. JOB : $16. ACTIVE : $3.; cards; 001001110 carpenter1 YES 002001110 plumber1 YES 003001110 painter1 YES 004001110 plumber1 YES 005001110 electrician1 YES 007001110 painter2 YES ; run; %utl_delta ( uinmem1 =oldrep, uinmem2 =newrep, uinkey =rep_socs, uotmem1 =repdelta, uotmem2= repsame ); %utl_delta; proc sort data=dat.oldrep out=ol;by rep_socs;run; proc sort data=dat.newrep out=nu;by rep_socs;run; title "Indepth comparison Updated RDMS table with New Feed"; proc compare data=ol compare=nu; run; */ %end; /* end macro argument checks */ %mend utl_delta; * create data; proc sort data=sashelp.class out=oldrep; by name; run; proc sql; create table new as select * from sashelp.class where 1=0; insert into new values ('Bill','M',13,65,82); quit; data newrep; set oldrep new; by name; if name='Alice' then delete; if name='Philip' then age=14; run; %utl_delta ( uinmem1 =oldrep, /* Last Months Data */ uinmem2 =newrep, /* Current Month Data */ uinkey =rep_socs, /* primary unique key both tables */ uotmem1 =repdelta, /* delta tble for RDBMS update */ uotmem2= repsame ); * apply the repdelta to make old look like new; %utl_update ( master=oldrep ,transaction=repdelta ,key=name ); ... View more

SAS Forum: Using '%else %do; .. %end;' instead of '%else ...;' - the missing ';' Code that consumes ';'s inspired; https://goo.gl/kEjeMW https://communities.sas.com/t5/General-SAS-Programming/Macro-giving-Syntax-error-after-compilation/m-p/324603 HAVE ==== Up to 40 obs WORK.DATES total obs=1 Obs DATE 1 15-06-2016 WANT ( This what the OPs macro will always does) ============================================== Up to 40 obs WORK.DATEIMPUTE total obs=1 Obs DATE DATEIMP 1 15-06-2016 15-06-2016 WORKING SOLUTION %else &vout.=&var.; USE %else %do; %str(&vout.=&var.;) %end; Better than %else &vout.=&var.;; I ran my debug macro on your code, only works in the old text editor. Highlight code and type debug on old text editor command line. see https://www.youtube.com/watch?v=JrxooHTx0c8 MPRINT(DEBUGA): data dateimpute; 822 +set dates; MPRINT(DEBUGA): set dates; 823 +DATEIMP=DATE run --- 824 +; 825 +; MPRINT(DEBUGA): DATEIMP=DATE run ; MPRINT(DEBUGA): ; 826 +run; MPRINT(DEBUGA): run; The problenm is a missing ';' on the last else. Changes Could %else &vout.=&var.; to %else &vout.=&var.;; However I like to use '%else %do;' everywhere for this reason There are over issues with this code %if %scan(&var.,2,'-')='' %then should be %if %scan(&var.,2,%str(-))= %then I like (Ian Whitlock did not like this?; %if "%scan(&var.,2,%str(-))"="" %then data dates; date='15-06-2016'; run;quit; %macro dtimpute(var=,vout=,mon=,day=); data dateimpute; set dates; %if &var.^='' %then %do; %if %scan(&var.,2,'-')='' %then %do; %if %scan(&var.,3,'-')='' %then &vout.=strip(&var.)||strip("-&mon.")||strip("-&day"); %else &vout.=%sysfunc(putn(%scan(&var.,1,'-'),'$4.'))||"-&mon.-"||%sysfunc(putn(%scan(&var.,3,'-'),'$2.')); %end; %else %if %scan(&var.,3,'-')='' %then &vout.=strip(&var.)||strip("-&day."); %else %do; %str(&vout.=&var.;) %end; run;quit; %end; run; %mend dtimpute; %dtimpute (var=DATE,vout=DATEIMP,mon=06,day=15); ... View more

For small datasets I don't think you can easily force SAS to use a HASH. /* T007370 SQL HASH MAGIC=103 faster that SQL Sort merge */ Hi Team I wanted to select about 1,000,000 records from a 5,000,000 table and add 100 variables at the same time. The finder file was 800mb. I noticed that the default SQL method(sort) took almost twice as long as the SQL(hash) method. Note that I have plenty of ram(128gb). The code below will not run in EG, memsize is set at 0.5gb. * sql sort=103 seconds; * sql hash 60 seconds; * there is an excellent paper in the most recent pharmasug on the sql magic option; * 800mb finder file with additional variables c1-c100; options compress=no; data find_benes(compress=no drop=i j); array chr[100] $8 c1-c100; do i=100000 to 10000000 by 10; pat_id=encrypt(i); do j=1 to 100; chr[j]=put(1e8*uniform(-1),z8.); end; output; end; run;quit; /* 800mb table Up to 40 obs from find_benes total obs=990001 pat_id C1 C100 1202046 75950697 28236507 1202056 22914180 68703933 1202066 61116305 19959418 1202076 58626531 82712843 1202086 10725652 84593862 */ * 4 gb; options compress=no; data all_benes(compress=no drop=i j); array chr[100] $8 d1-d100; do i=100000 to 10000000 by 2; pat_id=encrypt(i); do j=1 to 100; chr[j]=put(1e8*uniform(-1),z8.); end; output; end; run;quit; /* 4gb table with variables d1-d100 Up to 40 obs from all_benes total obs=4,950,001 pat_id D1 D100 1201766 90585571 28236507 1201768 55665932 68703933 1201770 29801506 19959418 1201772 67134560 82712843 1201774 74029386 84593862 */ * sql sort=103 seconds; * sql hash 60 seconds; proc sql _method magic=103; create table gotem as select l.* ,r.* from find_benes as l, all_benes as r where l.pat_id = r.pat_id ;quit; /* magic=101 = cartesian magic=102 = sort merge magic=103 = hash */ Note if you have good size ram 60-128gb you might want to consider using sasfile options load, open and close. for intermediate work datasets, but don't forget to free memory often. Unfortunately SAS does not have a means of using memory for the utility files and indexes are not supported. Perhaps a new kind of index is needed, in memory index. Sorting a 'big' dataset. I suspect using a hash with 7 systasks( split the big dataset into 7(firstobs-obs) pieces that sum to less then total dataset size might be much faster than the SAS sort on the big dataset, even though the sort is mutithreaded. Of course you need a lot of ram. ... View more

30 million rows is quite small, to get to 999gb the width of each row would be 33,000 bytes. I think you should normalize or look at a set of tables. Note that unless you need all 33,000 bytes separate tables would be much faster than processing 33,000 bytes records.   Even the census full sf1 detail is not this wide. ... View more