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.

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.

To Escape % Character in SAS PROC SQL

Suppose you want to run a query to find all records containing the pattern "REQUIRED" in a certain column, the SQL where clause will look something as follows:

where thetextfield like "%REQUIRED%"

But if you use it inside SAS, SAS will interpret the %REQUIRED as a macro and throw an error.  Here is how you can escape the % character:

%let pattern=%nrstr(%%)REQUIRED%str(%%);
proc sql;
create table new_dataset as
select * from dataset
where thetextfield like "&pattern.";
quit;

You can also put the code inside a macro, and have the pattern "REQUIRED", etc as a parameter to be passed into the macro.

%macro get_records_with_pattern(pattern);
    %let patternlike=%nrstr(%%)&pattern.%str(%%);
    proc sql;
    create table new_dataset as
    select * from dataset
    where thetextfield like "&patternlike.";
    quit;
%mend get_records_with_pattern;

Now you can call the macro with the parameter REQUIRED and many more.

%get_records_with_pattern(REQUIRED);
%get_records_with_pattern(ACCEPTED);
%get_records_with_pattern(PASSED);

Calculating Cumulative Percentage In PROC REPORT With ACROSS

I am finally able to get cumulative percentages calculated when /across is used in PROC REPORT.  Basically I have to programmatically calculate it in the code, and put the resulting value into the columns directly (such as _c4_ for column 4, _c7_ for column 7).  I contacted SAS technical support on this, and confirmed there was no way around this hard-coding of column number.  How sad!

Here is the SAS code, with column numbers hard-coded, but at least it works:

options missing=0;
proc report data = some_dataset nowd missing;
column credit_load snapshotdate,(ones ones=onespct cumpct) dummy;
define credit_load        / group 'Credit Load' ;
define snapshotdate     / across '' order=internal;
define ones                     / analysis sum 'Freq' style(column)={tagattr="format:##0"}    ;
define onespct               / analysis pctn 'Pct' style(column)={tagattr="format:##0.0%"};
define cumpct               / 'Cum Pct' style(column)={tagattr="format:##0.0%"};
define dummy              / noprint;

COMPUTE BEFORE ;
    cumtot1 = 0;
    cumtot2 = 0;
ENDCOMP;

COMPUTE dummy;
    cumtot1 + _c3_;
    cumtot2 + _c6_;

    _c4_ = cumtot1;
    _c7_ =  cumtot2;

    IF _BREAK_="_RBREAK_" THEN DO;
      _c4_=1;
      _c7_=1;
    END;
ENDCOMP;

rbreak after / summarize ;

run;