Monday, July 18, 2011

Date Manipulation in SAS

I need to produce a report that contains historical data of some sort based it's Tuesday snapshots in the year 2009 and 2010.  Then I found this Intnx function to help.  For example:

To get a list of Tuesdays starting Jan 6, 2010 into a dataset called tuesdays:
%let start_date='06jan2009'd;
data tuesdays ;
do i = 0 to 103;
    Tuesday=put(Intnx( 'Day' , &start_date ., i*7 ),date9.);
    output;
end;
drop i;
run ;
proc print data=tuesdays; run;
To load all Tuesday's SAS data files:
%let start_date='06jan2009'd;
%macro load_tuesday_sas_dataset;
    %global tuedate;
    %do i=0 %to 103;
        data _null_;
            call symput("tuedate",left(put(Intnx('Day',&start_date., &i. * 7),date9.)));
        run;
        %put &tuedate;
        data dataset_&tuedate.;
            set filelib.sasfile_&tuedate.;
        run;
    %end;
%mend load_tuesday_sas_dataset;
%load_tuesday_sas_dataset;
Enjoy learning SAS, a little bit at a time every day.

Friday, July 8, 2011

Reading Excel and Access files on SAS 64-bit

Finally got my machine rebuilt to Windows 7 64-bit, and had SAS 9.2.3 64-bit loaded.  Then I started to have problems running some SAS code to read Excel and Access files.  Error message is:
ERROR: Error trying to establish connection: Unable to create Data Source.: Class not
       registered
ERROR: Error in the LIBNAME statement.
The original SAS code for reading Excel and Access files are as follows:
libname xlsfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data source=c:\temp\ExcelFile.xlsx; extended Properties=Excel 12.0";

libname accfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\temp\AccessFile.accdb; Persist Security Info=True";
Working with SAS technical support, I found that our tech guy installed Office 32-bit on my machine.  SAS 64-bit could not use the 32-bit Office drivers.  But what I understand from technical support was even if I had Office 64-bit installed, I still would encounter some issues because 64-bit is officially supported in SAS version 9.3.  They mentioned something called PC Server that could help me.  I read up about it online, and eventually found a solution.

First, I have to install SAS PC Server component on my machine.  Then I need to change the SAS code to the followings accordingly:
libname xlsfile pcfiles type=Excel port=8621 server=localhost path = "c:\temp\ExcelFile.xlsx";

libname accfile pcfiles type=Access port=8621 server=localhost path = "c:\temp\AccessFile.accdb";
Thanks to this article DID YOU KNOW THAT YOU CAN NOW ACCESS PC FILES ON A WINDOWS MACHINE DIRECTLY FROM A UNIX MACHINE? which helped me solve the problem.