Using Batch Files to Automate

Batch files may be old fashioned but can save you a lot of time in repeated tasks.Here is an example of a batch file that has

1) Some Global variables (Inputs)

set SALES_GRP=s1
set ID=f2
set SALES_DATE=nov0107

#BATCH FILE COMMENT using hash order_date format is monddyy

2) Used notepad to open a file for making changes

call c:\software\Notepad.exe C:\folder1\input1.sas
call c:\software\Notepad.exe C:\folder2\input2.sas

3)Created a directory to store results

call mkdir C:\folder2\%SALES_GRP%\%ID%\%SALES_DATE%

4) Executed another batch file or code using relevant software (eg SAS.exe for SAS Code)

call C:\software\sas8.exe C:\folder1\input1.sas

call C:\software\sas8.exe C:\folder2\input2.sas


5) Changes active folder

call cd C:\folder1

This can particularly help you schedule SAS code running at specific times using Windows Scheduler

A Simple SAS Code to Automate Reports

Organizations use an increasing amount of reports. Countless hours are spent manipulating spreadsheets one after another, into and out of MS Access databases and finally emailing it to a select audience of people.

The following  tries to present a simple and integrated approach to report generation and presentation. This involves the use of Macros, for repeatability and flexibility.You can copy and paste the code directly to your SAS editor and make relevant changes.

Steps in preparing a report

  • Import data from data sources (spreadsheets, databases mostly .Here assuming BASE SAS ,only csv files are considered)
  • Manipulating data for analysis (in the case below the only data manipulation done is removal of duplicates. This section can be changed as needed by user for
    • Frequency counts using Proc Freq.
    • Mean, maximum and minimum counts using Proc Means.
    • Customized reports using Proc Report Procedure.)
  • Presenting and formatting data
  • Output data in the form of a report /spreadsheet.
  • Emailing internal customers of the report.

———————————————————————————————————

Using BASE SAS, the following can be coded as:

     /*PROGRAM TO AUTOMATE REPORTING */

    /*DECLARING THE PATH OF THE INPUT/OUTPUT FOLDER */

%let pathfile = ‘X:\Auto\AUG06-AUTOS\’ ;

     /*CREATING LIBRARY NAME */

libname auto &pathfile;

run;

   /*TO CONSERVE SPACE*/

options compress=yes;

   /*TO MAKE LOG READABLE */

options macrogen symbolgen;

/* STARTING MACRO FOR REPEATABLE EXECUTION OF REPORT*/

%macro impmth(mth,num,emailid);

 /*MANIPULATING VARIABLES TO REDUCE CHANGES */

data _null_ ;

call symput(‘filepath’,”‘”||&pathfile||&mth||’.csv’||”‘” );

call symput(‘unqpath’,”‘”||&pathfile||”unq”||&mth||’.csv’||”‘” );

call symput(‘unqxls’,”‘”||&pathfile||”unq”||&mth||’.xls’||”‘” );

run;

/*IMPORT*/

/*IMPORTING DATA FROM CSV FILES STORED IN FOLDER DECLARED ABOVE*/

PROC IMPORT OUT= auto.&num

DATAFILE= &filepath

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

quit;

/*CREATING VARIABLE FOR DISTINGUISHING INPUT*/

data auto.#

set auto.#

&num =1;

run;

———————————————————————–

/*DATA MANIPULATION HERE REMOVING DUPLICATES*/

proc sort data=auto.&num nodupkey;

by REFAGREEMENTID;

run;

———————————————————————–

/*EXPORT*/

/*EXPORTING DATA TO A CSV FILE*/

PROC EXPORT DATA= AUTO.&num

OUTFILE= &unqpath

DBMS=CSV REPLACE;

RUN;

/*EXPORTING DATA TO A XLS FILE*/

ODS HTML FILE=&unqxls;

proc print data=auto.#

run;

ODS HTML CLOSE;

/*EMAILING THE RESULTS*/

filename outbox email &emailid;

data _null_ ;

file outbox

to=(&emailid)/* Overrides value in filename statement */

cc=(&emailid)

subject=’File from’

attach=(&unqxls)

;

put ‘Hi,’;

put ‘This is an integrated approach to automating reports’;

put ‘It works great!’;

run;

%mend;

/*CALLING THE MACRO*/

/*HERE ‘Ahmedabad’ IS THE INPUT FILE WHILE Ahd IS THE OUTPUT FILE*/

/*HERE ‘aohri@hotmail.com’ IS THE EMAIL ID TO BE MAILED THE REPORT*/

%impmth(‘Ahmedabad’,Ahd,’aohri@hotmail.com’);

Note- This was written on version 8 of SAS, so let me know any issues.

Ajay 

Basic Text Mining :3 Simple Paths

The locals of Punjab (india). These are the tr...
Image via Wikipedia

Text Mining in which you search alpha numeric data for meaningful patterns is relatively more complex than plain numeric variable data crunching. The reason for that is human eye can measure only a few hundred rows of data before getting tired, and analytics software algorithms need to properly programmed else they miss the relevant solution or text. An example, how many Punjabis live in Delhi (Stats needed), suppose you have a Dataset that has all the names in Delhi,in order to send an sms contest (Marketing Decision) on Lohri (Punjabi specific Festival)

Text Manipulation can be done by TRIM and LOWER functions in EXCEL and corresponding functions in SAS. For Mining use the following options-

1)SAS Basic Text Mining -Using Only Base SAS

In SAS you can use the INDEXW function for text mining.

As per SAS Online DOc

INDEXW(source, excerpt)

Arguments

source
specifies the character expression to search.
excerpt
specifies the string of characters to search for in the character expression. SAS removes the leading and trailing blanks from excerpt.

The INDEXW function searches source, from left to right, for the first occurrence of excerpt and returns the position in source of the substring’s first character. If the substring is not found in source, INDEXW returns a value of 0. If there are multiple occurrences of the string, INDEXW returns only the position of the first

occurrence.”

2) MS EXCEL

You can use MS Excel for text mining too. I recommend Office 2007 simply because it can handle more rows.

The function in Excel is SEARCH

image

3) MS ACCESS

In MS Access you can use LIKE Queries to create a different table or append a value to certain columns

.Example

Some problems can?t be solved with comparisons : e.g. ?His name begins with Mc or Mac. In a case like this, wildcards are required, and are represented in SQL with the % sign and the LIKE keyword.

e.g.

SELECT au_lname, city

FROM authors

WHERE au_lname LIKE ?Mc&? or au_lanme LIKE ?Mac%?

UPDATED- The above post is now obsolete- there are easier and better ways to to text mining. That includes weka and R

The importance of measuring Error

Most Reporting Systems tend to focus on the positive,

sales targetted ,sales achieved, etc.

For quality reasons , it is important to measure variance or deviation from the mean. This can also be interpreted as measuring error for any planning, forecasting or performance measurement exercise.

It helps bring focus, by aiming to reduce the difference between planned/forecasted and actual performance by looking at the mean absolute percentage error. Also it is impportant to have estimates that increasingly move from qualitative metrics or scale variables to quantitative inputs for forecasting or planning.

The forecasts should be realistic based on data. The forecasted pleasure or displeasure of the boss/ end audience recieving the estimate tends to be the biggest practical input in planning exercises unfortunately.

That is why analytics is sometimes a corporate cultural thing as well.

1) Forecast honestly

2) Use as much quantitative inputs

3) Plan scenario analysis with varying probabilties

4) Measure error from forecast and actual in a feedback loop

5) Aim at reducing error.

What SIX SIGMA would call

Define

Measure

Analyze

Improve

Control

.This applies to decision making and planning at most organizations where data /information ends up too many times in floating spreadsheets or gatekeepers/old timers.

Ballmer + Yahoo = Google's Boo Hoo

” I am going to fucking kill Google” Quote alleged to Steve Ballmer , 2005-6 ,due to Google’s poaching of engineers.

The following  scenario is completely a  work of fiction.kill-goo.JPG

11 Ways Ballmer and Yahoo can kill Google –

1)  Make their search engine better ,try combining the cache , and retrival queries at least for the first page, use some innovation like www.ask.com and www.guruji.com rather than pain vanilla search.. Maybe some more billions to buy out www.baidu.com —- But thats the toughest

2) Poach engineers from Google —- Thats hard

3) Combine their online ,offline advertising forces and spin it off in a different company. So there is one software company and one media company.

 4) Use Gmail’s emails feature to give more space and advertise based on email content in yahoo email and hotmail. Cross promote OfficeLive on Yahoo and vice versa.

5)PRICE-  Offer key words in a cheaper version of Adwords. Start offering an free alternative to Adsense AND SQUEEZE the long tail of Google’s revenue.

6)Use the facebook deal to sell more ads, give better ROI to advertisers, and monitor click fraud using Microsoft’s OS.

7) Buy out www.automattic.com for the wordpress and get a social community (facebook stake enhancement ??)

8) Offer desktop advertising as a freeware for some microsoft programs .

9)Tweak explorer to click repeatedly or not at all or at a slower speed on all ads that have the words ads by Google.

10) Make all Yahoo sites and Microsoft off search by Google, but searchable by yahoo.

11) Hire lawyers to repeatedly file anti trust cases against Google 🙂

Ballmer + Yahoo = Google’s Boo Hoo

” I am going to fucking kill Google” Quote alleged to Steve Ballmer , 2005-6 ,due to Google’s poaching of engineers.

The following  scenario is completely a  work of fiction.kill-goo.JPG

11 Ways Ballmer and Yahoo can kill Google –

1)  Make their search engine better ,try combining the cache , and retrival queries at least for the first page, use some innovation like www.ask.com and www.guruji.com rather than pain vanilla search.. Maybe some more billions to buy out www.baidu.com —- But thats the toughest

2) Poach engineers from Google —- Thats hard

3) Combine their online ,offline advertising forces and spin it off in a different company. So there is one software company and one media company.

 4) Use Gmail’s emails feature to give more space and advertise based on email content in yahoo email and hotmail. Cross promote OfficeLive on Yahoo and vice versa.

5)PRICE-  Offer key words in a cheaper version of Adwords. Start offering an free alternative to Adsense AND SQUEEZE the long tail of Google’s revenue.

6)Use the facebook deal to sell more ads, give better ROI to advertisers, and monitor click fraud using Microsoft’s OS.

7) Buy out www.automattic.com for the wordpress and get a social community (facebook stake enhancement ??)

8) Offer desktop advertising as a freeware for some microsoft programs .

9)Tweak explorer to click repeatedly or not at all or at a slower speed on all ads that have the words ads by Google.

10) Make all Yahoo sites and Microsoft off search by Google, but searchable by yahoo.

11) Hire lawyers to repeatedly file anti trust cases against Google 🙂