Friday, October 28, 2011

How to create FTP path in SAS program

FILENAME FTPFILE FTP '' LS
HOST = "&FTPHST"
USER = "&FTPUSR"
PASS = "&FTPPAS"
CD = "&FTPCD\&PGMSEC";



DATA _NULL_;
INFILE FTPFILE;
INPUT;
IF _INFILE_ EQ "&PGMMTH" THEN CALL SYMPUT('LEVEL0','Y');
RUN;

FILENAME FTPFILE FTP '' LS
HOST = "&FTPHST"
USER = "&FTPUSR"
PASS = "&FTPPAS"
CD = "&FTPCD\&PGMSEC"
RCMD = "MKD &PGMMTH";


DATA _NULL_;
IF "&LEVEL0" EQ 'N' THEN
DO;
INFILE FTPFILE;
INPUT;
END;
RUN;

Thursday, October 6, 2011

SUBTOTAL -- my first VBA program

'Calculate subtotal of numeric columns below the cell selected

Option Explicit

Sub subtotal()

Dim result, data As Range
Dim lastrow As Long

If TypeName(Selection) <> "Range" Then Exit Sub

Set result = Selection

lastrow = ActiveSheet.UsedRange.Rows.Count
Set data = Range(Selection.Offset(2, 0), Cells(lastrow, result.Column))
result.Formula = "=SUBTOTAL(9," & data.Address(external:=True) & ")"
End Sub

Monday, June 6, 2011

SAS Macro: How to Retrieve a Value from a Dataset

Solution 1:

%MACRO Get_data(myDataset=,myLine=,myColumn=,myMVar=);
%GLOBAL &myMVar.;
data _null_;
set &myDataset.;
if _N_ = &myLine. then do;
call symput(symget('myMVar'),&myColumn.);
end;
run;
%MEND Get_data;

Solution 2:

%MACRO Get_data(myDataset=,myLine=,myColumn=,myMVar=);
%GLOBAL &myMVar;
proc sql noprint;
select &myColumn into :&myMVarfrom &myDatasetwhere monotonic() = &myLine;
quit;
%MEND Get_data;
%Get_data(myDataset=tablename,myLine=linenumber,myColumn=colname,myMVar=varname)
%put &varname;

Solution 3: USE SAS I/O functions!

%MACRO Get_data(myDataset,myLine,myColumn);
%global myMvar;
%let dsid=%sysfunc(open(&myDataset.,i));
%let rc=%sysfunc(fetchobs(&dsid, &myLine.));
%if &rc = 0 %then
%let myMvar=%sysfunc(GETVARN(&dsid,%sysfunc(varnum(&dsid,&myColumn))));
/****use getvarc for character variables*****/
%let rc=%sysfunc(close(&dsid));
%MEND Get_data;

MORE SAS CODES

http://www.datasavantconsulting.com/roland/Spectre/maclist2.html

Put all variables except automatic variables in a file

put (_all_)(+0);

Friday, June 3, 2011

Route SAS log and procedure ouput using PROC PRINTTO

PROC PRINTTO is very useful when we try to automate the debugging process of SAS program.

LABEL= provide a description for a SAS log or procedure output stored in a SAS catalog entry
LOG= route the SAS log to a permanent external file or SAS catalog entry
LOG= and PRINT= with same destination combine the SAS log and procedure output into a single file
NEW= replace the file instead of appending to it
PRINT= route procedure output to a permanent external file or SAS catalog entry

Using a PROC PRINTTO statement with no options closes any files opened by a PROC PRINTTO statement points both the SAS log and SAS procedure output to their default destinations.

Example:

options nodate pageno=1 linesize=80 pagesize=60 source;

proc printto log='log-file';
run;

data numbers;
input x y z;
datalines;
14.2 25.2 96.8
10.8 51.6 96.8
9.5 34.2 138.2
8.8 27.6 83.2
11.5 49.4 287.0
6.3 42.0 170.7
;

proc printto print='output-file' new;
run;proc print data=numbers;
title 'Listing of NUMBERS Data Set';
run;

proc printto;
run;

Wednesday, June 1, 2011

Second day at work!

I am so bored right now.

I still don't have access to mainframe SAS, so I can't look at codes and try out things. More important, I listened to people talking about their works, and unfortunately realized that this is not an exciting job as I had before.

When you trade, even you are not well paid, it's still exciting. But when you look at others' trading numbers, it's certainly like a whole world apart. Besides these, this job is such a long hour job, that it hardly justify the salary. OK, I admit that most people don't earn as much as I did. I was spoiled. But I feel I deserve better. Who cares what I think!

The bright spot of this job is the company title comes with it. Is this all about? Why I am here?
My manager have spent 1 hour persuading her underling to work on July 4th, now I feel sorry for her.

I hope my son Sam had a wonderful day.

Tuesday, May 31, 2011

ALL THE FILES, PLEASE

A number of ways to read multiple files in one data set.


First, just list the files in a series of DATALINES
in the DATA step.
DATA one;
LENGTH fil2read $ 40;
INPUT fil2read $;
INFILE dummy FILEVAR=fil2read
END=done;
DO WHILE (NOT done);
INPUT lastn $ firstn $
hiredate : mmddyy8.
salary;
OUTPUT;
END;
DATALINES;
D:\Infile\emplist.dat
D:\Infile\emplist1.dat
D:\Infile\emplist2.dat
D:\Infile\emplist3.dat
D:\Infile\emplist4.dat
RUN;



Be careful to set up the DO loop so that the DATA step never gets to the End-Of-File marker on any file. Using the END= option on the second INFILE statement sets up a temporary variable (done) which will register 0 (not the last record) or 1 (the last record) for each raw data line read
in from each file. This is necessary since, if SAS reads in any End-of- File marker, the DATA step closes.
By testing for DONE at the top of the loop (DO WHILE), and exiting the DO loop after the last line of every file, we ensure that we never hit the end-of-file for all files read in. This remains true even for empty files.


A SAS Dataset can be used to store the names of the files and would be called using a SET statement.

DATA one;
set two;
INFILE dummy FILEVAR=fil2read
END=done;
DO WHILE (NOT done);
INPUT lastn $ firstn $
hiredate : mmddyy8.
salary;
OUTPUT;
END;
RUN;



Finally, it’s possible to read in filenames dynamically, using a FILENAME with the Pipe option. This is useful when all of the files are in the same directory. With the PIPE keyword, the FILENAME statement can take an operating system command in quotes, and accept the result as valid input. Unfortunately, this is not available on Mainframe operating systems.


FILENAME indata PIPE "dir D:\Infile\*.dat /b";
DATA test;
LENGTH fil2read $40;
INFILE indata MISSOVER;
INPUT fil2read $;
fil2read="d:\infile\"fil2read;
INFILE dummy FILEVAR=fil2read END=done;
DO WHILE(NOT done);
INPUT lastn $ firstn $ hiredate : mmddyy8. salary;
OUTPUT;
END;
RUN;



The information returned from the FILENAME statement is a list of all files in D:\Infile with a .DAT type. One can specify all files, or (as above) specific files. The DATA step can use this information with one INFILE statement and then use the information to read the files by
applying it to a FILEVAR= option on a second INFILE statement.

One limitation is that the Windows command (DIR) returns only the names of the files without the pathnames. So the fil2read variable needs to be augmented with the pathname in an
assignment statement. fil2read="d:\infile\"fil2read;
In UNIX, a similar FILENAME statement would read:

FILENAME indata PIPE "ls -l /Infile/*.dat /b";

The UNIX ls command returns a fully qualified path and filename.

MISSOVER, TRUNCOVER and PAD

MISSOVER was originally created to be used in conjunction with PAD and works effectively and well in most situations. However, this can be a CPU intensive process when reading an extremely large file.

STOPOVER is a good tool for checking code and raw data when dealing with large, potentially messy files, since it forces the DATA step to stop the first time it finds a short line.

TRUNCOVER was developed later than the MISSOVER and PAD options, and deals admirably with not only short lines but with short values. TRUNCOVER is more also efficient since it doesn't require the extra "padding".

Friday, April 22, 2011

Complicated declarations & definitions

As an aside, once you figure out how the C and C++ declaration syntax works you can create much more complicated items. For instance:

//: C03:ComplicatedDefinitions.cpp  
/* 1. */     void * (*(*fp1)(int))[10];  
/* 2. */     float (*(*fp2)(int,int,float))(int);  
/* 3. */     typedef double (*(*(*fp3)())[10])();              
fp3 a;  
/* 4. */     int (*(*f4())[10])();  int main() {} ///:~

Walk through each one and use the right-left guideline to figure it out. Number 1 says “fp1 is a pointer to a function that takes an integer argument and returns a pointer to an array of 10 void pointers.”

Number 2 says “fp2 is a pointer to a function that takes three arguments (int, int, and float) and returns a pointer to a function that takes an integer argument and returns a float.”

If you are creating a lot of complicated definitions, you might want to use a typedef. Number 3 shows how a typedef saves typing the complicated description every time. It says “An fp3 is a pointer to a function that takes no arguments and returns a pointer to an array of 10 pointers to functions that take no arguments and return doubles.” Then it says “a is one of these fp3 types.” typedef is generally useful for building complicated descriptions from simple ones.

Number 4 is a function declaration instead of a variable definition. It says “f4 is a function that returns a pointer to an array of 10 pointers to functions that return integers.”

You will rarely if ever need such co

When to use reinterpret_cast?

The C++ standard guarantees the following:

static_casting a pointer to and from void* preserves the address. That is, in the following, a, b and c all point to the same address:

int* a = new int();
void* b = static_cast<void*>(a);
int* c = static_cast<int*>(b);

reinterpret_cast only guarantees that if you cast a pointer to a different type, and thenreinterpret_cast it back to the original type, you get the original value. So in the following:

int* a = new int();
void* b = reinterpret_cast<void*>(a);
int* c = reinterpret_cast<int*>(b);

a and c contain the same value, but the value of b is unspecified. (in practice it will typically contain the same address as a and c, but that's not specified in the standard, and it may not be true on machines with more complex memory systems.

For casting to and from void*, static_cast should be preferred.

One case when reinterpret_cast is necessary is when interfacing with opaque data types. This occurs frequently in vendor APIs over which the programmer has no control. Here's a contrived example where a vendor provides an API for storing and retrieving arbitrary global data:

// vendor.hpp
typedef struct _Opaque * VendorGlobalUserData;
void VendorSetUserData( VendorGlobalUserData p );
VendorGlobalUserData VendorGetUserData();

To use this API, the programmer must cast their data to VendorGlobalUserData and back again.static_cast won't work, you must use reinterpret_cast:

// main.cpp
#include "vendor.hpp"
#include
using namespace std;

struct MyUserData {
MyUserData() : m( 42 ) {}
int m;
};

int main() {
MyUserData u;

// store global data
VendorGlobalUserData d1;
// d1 = &u; // compile error
// d1 = static_cast< VendorGlobalUserData >( &u ); // compile error
d1
= reinterpret_cast< VendorGlobalUserData >( &u ); // ok
VendorSetUserData( d1 );

// do other stuff...

// retrieve global data
VendorGlobalUserData d2 = VendorGetUserData();
MyUserData * p = 0;
// p = d2; // compile error
// p = static_cast< MyUserData * >( d2 ); // compile error
p
= reinterpret_cast< MyUserData * >( d2 ); // ok

if ( p ) { cout << p->m << endl; }
return 0;
}

Below is a contrived implementation of the sample API:

// vendor.cpp
static VendorGlobalUserData g = 0;
void VendorSetUserData( VendorGlobalUserData p ) { g = p; }
VendorGlobalUserData VendorGetUserData() { return g; }