WENSUI'S BLOG IN STATISTICAL COMPUTING

NEW BLOG SPACE

2006-08-13T02:05:00.000-04:00

I am moving my blog to a new space and won't update this one any more.

For my new blog, please visit http://statcompute.spaces.live.com.

Thank you so much!

WHEN SAS MEETS R: A MACRO TO IMPORT .sav OR .dta FILE

2006-03-29T23:17:00.000-05:00

%macro foreign(file = , data = );
/************************************************
* THIS MACRO IS MOTIVATED BY FOREIGN PACKAGE IN *
* R LANGUAGE AND DESIGNED TO READ SPSS *.sav OR *
* STATA *.dta DATA FILE. *
*************************************************
* REQUIREMENTS TO RUN THIS MACRO: *
* 1. INSTALL R LANGUAGE (www.r-project.org) *
* 2. INSTALL FOREIGN PACKAGE *
*************************************************
* MACRO INPUTS: *
* 1. file: full path of data file. *
* (example: data = c:\temp\test.sav) *
* 2. data: name of output SAS table *
*************************************************
* AUTHOR: WENSUI LIU (wensui.liu@cchmc.org) *
* DATE : 23MAR, 2006 *
************************************************/

options mlogic mprint symbolgen;

/* PATH THAT R IS INSTALLED ON PC */
%let rpath = C:\Program Files\R\bin\;

data _null_;
/* CHANGE PATH OF DATA FILE THAT R CAN RECOGNIZE */
in = tranwrd("&file", "\", "\\");
call symput('in', trim(in));
/* ASSIGN THE DIRECTORY OF DATA FILE TO MACRO VARIABLE */
path = substr("&file", 1, index("&file", scan("&file", -1, "\")) - 1);
call symput('path', trim(path));
/* ASSIGN NAME OF R FILE TO MACRO VARIABLE */
r = trim(path)||"foreign.r";
call symput('r', trim(r));
/* ASSIGN PATH OF OUTPUT *.csv FILE TO MACRO VARIABLE */
csv = tranwrd(trim(path)||"&data"||".csv", '\', '\\');
call symput('csv', trim(csv));
run;

data _null_;
/* GET THE EXTENSION OF DATA FILE, .sav OR .dta */
ext = upcase(scan("&file", -1, "."));
/* OUTPUT A FILE WITH R CODE */
file "&r";
/* WRITE R CODE TO OUTPUT R FILE */
put 'cat("\n", "***** Log of R execution *****", "\n\n");';
put "library(foreign);";
if ext = 'SAV' then do;
imp = "&data"||'<-read.spss(file = "'||"&in"||'", '||
"use.value.labels = FALSE, to.data.frame = TRUE);";
end;
if ext = 'DTA' then do;
imp = "&data"||'<-read.dta(file = "'||"&in"||'", '||
"convert.underscore = FALSE);";
end;
put imp;
exp = 'write.table('||"&data"||', file = "'||"&csv"||'", '||
'sep =",", row.names = FALSE, na = " ");';
put exp;
put 'quit("no");';

/* ASSIGN SHELL SCRIPT TO RUN R LANGUAGE TO MACRO VARIABLE */
cmd = "'"||'"'||"&rpath.Rcmd.exe"||'" BATCH -- "'||
"&r"||'"'||"'";
call symput('cmd', trim(cmd));
run;

/* RUN R CODE IN BATCH MODE */
options XSYNC NOXWAIT XMIN;
x &cmd;

/* IMPORT *.csv FILE OUTPUT BY R INTO SAS */
proc import datafile = "&path.&data..csv"
out = &data replace;
getnames = YES;
run;

data _null_;
/* INFILE R OUTPUT */
infile "&r..Rout";
input;
/* SHOW R OUTPUT IN SAS LOG WINDOW */
if _n_ > 20 then put _infile_;
/* ASSIGN NAMES OF INTERMEDIATE FILES TO MACRO VARIABLES */
del1 = "'del "||'"'||"&r"||'"'||"'";
call symput('del1', trim(del1));
del2 = "'del "||'"'||"&path.&data..csv"||'"'||"'";
call symput('del2', trim(del2));
del3 = "'del "||'"'||"&r..Rout"||'"'||"'";
call symput('del3', trim(del3));
run;

/* DELETE INTERMEDIATE FILES */
systask command &del1;
systask command &del2;
systask command &del3;
/************************************************
* END OF MACRO *
************************************************/
%mend foreign;

%foreign(file = c:\temp\depression_pp.sav, data = ppsav);

SAS CODE ABLE TO RECOGNIZE ITS OWN PATH

2006-03-11T10:53:00.000-05:00

/**************************************
* THIS CODE IS ABLE TO RECOGNIZE THE *
* PATH IT IS IN BASED ON ITS OWN NAME *
**************************************/

/* SAS FILE SAVED AS MYSAS.SAS */
%let file = MySAS;

proc sql noprint;
select distinct
substr(xpath,1,(index(xpath,"&file..sas") -1))
into
:path
from
dictionary.extfiles
where
index(upcase(xpath), upcase("&file..sas")) > 0;
quit;

%put &path;

SAS MACRO OF VARIABLES SELECTION BY BOOTSTRAP (Modelwise)

2006-03-01T20:29:00.000-05:00

%macro bootglm(data = , dv = ,iv = , class = none, n = 10);

/***********************************************************
* *
* THIS MACRO IS TO ASSESS THE IMPORTANCE OF INDEPENDENT *
* VARIABLES IN GENERAL LINEAR MODEL USING BOOTSTRAP *
* *
************************************************************
* *
* EXPLANATION OF THE MACRO *
* *
* data : INPUT DATASET, *
* dv : DEPENDENT VARIABLE, SAME AS LHS OF MODEL *
* STATEMENT IN PROC GLM *
* iv : INDEPENDENT VARIABLES, SAME AS RHS OF MODEL *
* STATEMENT IN PROC GLM *
* class: CLASS VARIABLES IF NECESSARY, SAME AS INPUT *
* VARIABLES OF CLASS STATEMENT IN PROC GLM *
* n : NUMBER OF TIMES DRAWING RANDOM SAMPLES WITH *
* REPLACEMENT FROM DATASET *
* *
************************************************************
* *
* AUTHOR: WENSUI LIU (WENSUI.LIU@CCHMC.ORG), 01-MAR-2006 *
* *
***********************************************************/

proc sql noprint;
/* CREATE AN EMPTY TABLE TO HOLD OUTPUT OF GLM */
create table glm_result
(
iv char(10),
prob num format = 6.4,
sig1 num format = 4.,
sig2 num format = 4.,
sig3 num format = 4.,
sig4 num format = 4.
);

/* ASSIGN # OF RECORDS TO A MACRO VARIABLE &sample */
select count(*) into :sample from &amp;data;
quit;

/* START THE LOOP FOR RANDOM SAMPLING FROM DATASET */
%do i = 1 %to &n;

/* DRAW RANDOM SAMPLE WITH REPLACEMENT FROM DATASET */
proc surveyselect data = &data method = urs
out = &data._tmp n = &sample noprint;
run;

/* SUPPRESS OUTPUT OF PROC GLM */
ods exclude all;

/* MODEL GLM WITHOUT CATEGORICAL VARIABLES */
%if &class = none %then %do;
proc glm data = &data._tmp outstat = model_tmp;
freq numberhits;
model &dv = &amp;amp;iv;
run; quit;
%end;
/* MODEL GLM WITH CATEGORICAL VARIABLES */
%else %do;
proc glm data = &data._tmp outstat = model_tmp;
freq numberhits;
class &class;
model &dv = &amp;amp;iv;
run; quit;
%end;
ods exclude none;

/* INSERT MODEL OUTPUT INTO TABLE glm_result */
proc sql;
insert into glm_result
select _source_ as iv, prob,
case when prob <= 0.01 then 1 else 0 end as sig1, case when prob > 0.01 and prob <= 0.05 then 1 else 0 end as sig2, case when prob > 0.05 and prob <= 0.1 then 1 else 0 end as sig3, case when prob > 0.1 then 1 else 0 end as sig4
from model_tmp
where _type_ in ("SS3");
quit;

/* END OF LOOP FOR RANDOM SAMPLE */
%end;

proc summary data = glm_result nway;
class iv;
output out = out_table (drop = _type_ rename = (_freq_ = count))
mean(prob) = prob uclm(prob) = uprob lclm(prob) = lprob
sum(sig1) = sum(sig2) = sum(sig3) = sum(sig4) = ;
run;

/* DELETE UNUSEFUL TABLES */
proc datasets library = work nolist;
delete exp_tmp model_tmp glm_result;
run; quit;

proc sort data = out_table;
by prob;
run;

/* REPORT THE FINAL RESULT */
proc report data = out_table nowd headline headskip
split = '*' formchar(2) = '=';
title;
column ("Summary of Variable Importance* *
For General Linear Model* * *
Dependent Variable: &dv * *"
iv sig1 sig2 sig3 sig4 count prob uprob lprob);

define iv / "Independent*Variable" left width = 11;
define sig1 / "# of*1% Sig" center width = 8;
define sig2 / "# of*5% Sig" center width = 8;
define sig3 / "# of*10% Sig" center width = 8;
define sig4 / "# of*Insig" center width = 8;
define count / "Total*Models" center width = 8 format=4.;
define prob / "Average*Prob" center width = 8;
define uprob / "95% UCL*of Prob" center width = 8;
define lprob / "95% LCL*of Prob" center width = 8;
run;

%mend bootglm;

SAS MACRO OF VARIABLES SELECTION BY BOOTSTRAP (Pairwise)

2006-02-19T14:47:00.000-05:00

%macro biboot(data = , dv = ,iv = , class = none, n = 10);

/***********************************************************
* *
* THIS MACRO IS TO BUILD BIVARIATE LINEAR MODEL BETWEEN *
* DEPENDENT VARIABLE AND INDEPENDENT VARIABLE ONE BY ONE *
* USING BOOTSTRAP AND EVALUATE THE IMPORTANCE OF EACH *
* INDEPENDENT VARIABLE *
* *
************************************************************
* *
* EXPLANATION OF THE MACRO *
* *
* data : INPUT DATASET, *
* dv : DEPENDENT VARIABLE, SAME AS LHS OF MODEL *
* STATEMENT IN PROC GLM *
* iv : INDEPENDENT VARIABLES, SAME AS RHS OF MODEL *
* STATEMENT IN PROC GLM *
* class: CLASS VARIABLES IF NECESSARY, SAME AS INPUT *
* VARIABLES OF CLASS STATEMENT IN PROC GLM *
* n : NUMBER OF TIMES DRAWING RANDOM SAMPLES WITH *
* REPLACEMENT FROM DATASET *
* *
************************************************************
* *
* AUTHOR: WENSUI LIU (WENSUI.LIU@CCHMC.ORG), 16-FEB-2006 *
* *
***********************************************************/

data _null_;
/* SEPARATE EACH INDEPENDENT BY A "+" SIGN */
indep = tranwrd(compbl("&iv"), " ", "+");
/* COUNT # OF INDEPENDENT VARIABLES */
indepn = count(tranwrd(compbl("&iv"), " ", "+"), "+") + 1;
/* ASSIGN # OF INDEPENDENTS TO A MACRO VARIABLE &indepn */
call symput('indepn', put(indepn, 3.));
/* ASSIGN ALL INDEPENDENTS TO A MACRO VARIABLE &indep */
call symput('indep', indep);
run;

proc sql noprint;
/* CREATE AN EMPTY TABLE TO HOLD OUTPUT OF GLM */
create table glm_result
(
iv char(20),
df num,
f num,
prob num format = 8.4,
flag char(5)
);
/* ASSIGN # OF RECORDS TO A MACRO VARIABLE &sample */
select count(*) into :sample from &data;
quit;

/* START THE LOOP FOR RANDOM SAMPLING FROM DATASET */
%do j = 1 %to &n;

/* DRAW RANDOM SAMPLE WITH REPLACEMENT FROM DATASET */
proc surveyselect data = &data method = urs
out = &data._tmp n = &sample seed = &j noprint;
run;

/* START THE LOOP FOR EACH INDEPENDENT VARIABLE */
%do i = 1 %to &indepn;

/* ASSIGN ith INDEPENDENT TO MACRO VARIABLE &variable */
%let variable = %scan(&indep, &i, +);

/* SUPPRESS OUTPUT OF PROC GLM */
ods exclude all;

/* MODEL GLM WITHOUT CATEGORICAL VARIABLES */
%if &class = none %then %do;
proc glm data = &data._tmp outstat = model_tmp;
freq numberhits;
model &dv = &amp;variable;
run; quit;
%end;
/* MODEL GLM WITH CATEGORICAL VARIABLES */
%else %do;
proc glm data = &data._tmp outstat = model_tmp;
freq numberhits;
class &class;
model &dv = &amp;variable;
run; quit;
%end;
ods exclude none;

/* INSERT MODEL OUTPUT INTO TABLE glm_result */
proc sql;
insert into glm_result
select _source_ as iv, df, f, prob,
case
when prob <= 0.01 then "sig1" when prob > 0.01 and prob <= 0.05 then "sig2" when prob > 0.05 and prob <= 0.1 then "sig3" else "sig4" end as flag from model_tmp where _type_ in ("SS1"); quit; /* END OF LOOP FOR VARIABLES */ %end; /* END OF LOOP FOR RANDOM SAMPLE */ %end; proc sql; /* COUNT # OF SIGNIFICANT MODELS FOR EACH INDEPENDENTS */ create table out_table as select iv, sum(case when flag = "sig1" then 1 else 0 end) as sig1, sum(case when flag = "sig2" then 1 else 0 end) as sig2, sum(case when flag = "sig3" then 1 else 0 end) as sig3, sum(case when flag = "sig4" then 1 else 0 end) as sig4, count(*) as total from glm_result group by iv; quit; /* DELETE UNUSEFUL DATASETS */ proc datasets library = work nolist; delete &data._tmp model_tmp glm_result; quit; /* SORT THE TABLE BY COUNTS OF SIGNIFICANCE */ proc sort data = out_table; by descending sig1 descending sig2 descending sig3 descending sig4; run; /* REPORT THE FINAL RESULT */ proc report data = out_table nowd headline headskip split = '*' formchar(2) = '='; title; column ("Summary of Variable Importance* * Based on Bivariate General Linear Model* * * Dependent Variable: &dv * *" iv sig1 sig2 sig3 sig4 total); define iv / "Independent*Variable" left; define sig1 / "Count of*1% Sig" center width = 10; define sig2 / "Count of*5% Sig" center width = 10; define sig3 / "Count of*10% Sig" center width = 10; define sig4 / "Count of*Insig" center width = 10; define total / "Count of*Total Models" center width = 20; run; %mend biboot;

SAS MACRO OF PAIRWISE VARIABLES SELECTION IN GLM

2006-02-16T18:28:00.000-05:00

%macro biglm(data = , dv = ,iv = , class = none);

/***************************************************************************
* THIS MACRO WILL DO A BIVARIATE GENERAL LINEAR MODEL BETWEEN DEPENDENT *
* VARIABLE AND INDEPENDENT VARIABLES ONE BY ONE, COMPUTE F VALUE AND ITS *
* PROBABILITY, FLAG OUT SIGNIFICANT VARIABLES, AND SORT ALL VARIABLES BY *
* THEIR F-VALUES FROM HIGH TO LOW. *
* IT CAN BE USED TO SELECT IMPORTANT VARIABLES IN A GENERAL LINEAR MODEL *
* *
****************************************************************************
* *
* EXPLANATION OF THIS MACRO *
* *
* DATA : INPUT DATASET, *
* DV : DEPENDENT VARIABLE, SAME AS LHS OF MODEL STATEMENT IN PROC GLM *
* IV : INDEPENDENT VARIABLES, SAME AS RHS OF MODEL STATEMENT IN PROC GLM *
* CLASS: CLASS VARIABLES IF NECESSARY, SAME AS INPUT VARIABLES OF CLASS *
* STATEMENT IN PROC GLM *
* *
****************************************************************************
* AUTHOR: WENSUI LIU (WENSUI.LIU@CCHMC.ORG), 16-FEB-2006 *
***************************************************************************/

data _null_;
iv2 = tranwrd(compbl("&iv"), " ", "+");
n = count(tranwrd(compbl("&iv"), " ", "+"), "+") + 1;
call symput('n', put(n, 3.));
call symput('iv2', iv2);
run;

proc sql;
create table result
(
iv char(20),
df num,
fvalue num,
probf num format = 8.4,
flag char(5)
);
quit;

%do i = 1 %to &n;

%let v = %scan(&iv2, &i, +);
%put &v;

ods output ModelANOVA = model_tmp1
(where = (HypothesisType = 1));

ods listing close;
%if &class = none %then %do;
proc glm data = &data;
model &dv = &v;
run; quit;
%end;
%else %do;
proc glm data = &data;
class &class;
model &dv = &v;
run; quit;
%end;
ods listing;

proc sql;
insert into result
select
source as iv, df, fvalue, probf,
case
when probf <= 0.01 then "***" when probf > 0.01 and probf <= 0.05 then "**" when probf > 0.05 and probf <= 0.1 then "*" else "" end as flag from model_tmp1; quit; %end; proc sort data = result; by probf; run; proc report data = result nowd headline center headskip split = '*' formchar(2) = '='; title; column ("Summary of Variable Importance* *Based on Bivariate General Linear Model* * * Dependent Variable: &dv * *" iv df fvalue probf flag); define iv / "Independent*Variable" center; define df / "Degree of*Freedom" center; define fvalue / "F Value" format = 12.2 center; define probf / "Pr > F" center;
define flag / " ";
compute after;
line " ";
line "***: 1% Significance Level";
line "** : 5% Significance Level";
line "* : 10% Significance Level";
endcomp;
run;

%mend;

HIGHLIGHT VALUES IN A REPORT USING SAS

2006-01-12T09:22:00.000-05:00

********************************
* HIGHLIGHT VALUES IN A REPORT *
* USING PROC REPORT OF SAS *
* DATE: 12JAN, 2006 *
********************************;

/* SIMULATE RANDOM NUMBERS */
data one;
do i = 1 to 10;
id = put(i, z3.);
x = rannor(1);
output;
end;
run;

/* DELIVER THE REPORT TO EXCEL FILE BY ODS */
ods html body = 'c:\test.xls' style = minimal;

proc report data = one nowd headline split = "*";
title;
column("THIS IS*A TEST" id x);

define id / "ID"
style = {font_weight = bold htmlstyle = "vnd.ms-excel.numberformat:@"};
define x / 'RANDOM*NUMBER' format = 4.2
style = {htmlstyle = "vnd.ms-excel.numberformat:0.00"};

/* HIGHLIGHT EXTREME VALUES */
compute x;
if _c2_ >= 1 then do;
call define(_col_, "style",
"style = [background = red foreground = green font_weight = bold");
end;
else if _c2_ <= 0 then do; call define(_col_, "style", "style = [background = green foreground = red font_weight = bold");
end;
endcomp;
run;
ods html close;
***************
* END OF CODE *
***************;

INTERACT R WITH SQLITE USING RSQLITE

2005-12-31T01:04:00.000-05:00

##################################################
# INTERACT R WITH SQLITE USING RSQLITE #
# DATE : DEC-31, 2005 #
##################################################

# LOAD LIBRARIES AND DATA FRAME
library(DBI);
library(RSQLite);
library(Hmisc);
data(iris);

# SET WORKING DIRECTORY
setwd('f:\\application\\sqlite');

# CRATE A SQLITE DATABASE NAMED IRIS.DB
shell('sqlite3 iris.db');

# DEFINE DATABASE DRIVER
driver<-dbDriver("SQLite");

# DEFINE CONNECTION TO IRIS.DB
connect<-dbConnect(driver, dbname = "iris.db");

# WRITE IRIS DATA FRAME INTO TABLE TBL_IRIS OF DATABASE
dbWriteTable(connect, "tbl_iris", iris, overwrite = T, row.names = F, eol = "\r\n" );

# LIST ALL TABLES IN THE DATABASE
dbListTables(connect);

# QUERY DATA AGAINST TBL_IRIS IN THE DATABASE
query01<-dbSendQuery(connect, statement = "select * from tbl_iris");

# FETCH 10 RECORDS OF DATA INTO A DATA FRAME
data01<-fetch(query01, n = 10);

# PRINT CONTENTS OF DATA FRAME
contents(data01);

# CLOSE THE QUERY
sqliteCloseResult(query01);

# CLOSE THE CONNECTION
sqliteCloseConnection(connect);

# CLOSE THE DRIVER
sqliteCloseDriver(driver);

READ DATA FROM CONSOLE OF R USING SCAN()

2005-12-30T16:01:00.000-05:00

##################################################
# READ DATA INTO DATA FRAME FROM CONSOLE IN R #
# DATE : DEC-30, 2005 #
##################################################
# EQUIVALENT SAS CODE:
# data person;
# infile cards firstobs = 2;
# input name $ dept $ salary;
# cards;
# this is a test
# John Sales 78000
# Mary Acct 50000
# ;
# run;
##################################################

# PRINT DATA INTO A FILE
cat("this is a test", "John Sales 78000", "Mary Acct 50000", file = "person.data", sep = "\n");

# READ DATA FROM FILE USING SCAN()
person<-data.frame(scan(file = "person.data", what = list(name = character(), dept = character(), salary = numeric()), skip = 1));

SUBSET DATA FRAME IN R

2005-12-30T01:27:00.000-05:00

##################################################
# METHODS TO SUBSET A DATA FRAME IN R #
# DATE : DEC-30, 2005 #
##################################################

# LOAD HMISC LIBRARY
library(Hmisc);

# PULL IRIS DATA
data(iris);
# SUMMARIZE IRIS DATA
summary(iris);

# 1ST METHOD TO SUBSET IRIS DATA
iris.subset01<-iris[iris$Species=="setosa" & iris$Sepal.Length>=5,];
# SUMMARIZE THE SUBSET
summary(iris.subset01);

# 2ND METHOD TO SUBSET IRIS DATA
iris.subset02<-subset(iris, Species=="virginica" & Petal.Width<=2); # SUMMARIZE THE SUBSET summary(iris.subset02);

CALCULATE GROUP SUMMARY IN R

2005-12-13T00:44:00.000-05:00

##################################################
# HOW TO CALCULATE GROUP SUMMARY IN R #
# DATE : DEC-13, 2005 #
##################################################
# EQUIVALENT SAS CODE: #
# #
# DATA DATA; #
# DO I = 1 TO 2; #
# DO J = 1 TO 4; #
# GROUP = 'TREATMENT_'||PUT(I, 1.); #
# X = RANNOR(1); #
# OUTPUT; #
# END; #
# END; #
# KEEP GROUP X; #
# RUN; #
# #
# PROC SQL; #
# CREATE TABLE COMBINE AS #
# SELECT *, MEAN(X) AS MEAN_X, SUM(X) AS SUM_X #
# FROM DATA #
# GROUP BY GROUP; #
# QUIT; #
##################################################

# GENERATE A TREATMENT GROUP #
group<-as.factor(paste("treatment", rep(1:2, 4), sep = '_'));

# CREATE A SERIES OF RANDOM VALUES #
x<-rnorm(length(group));

# CREATE A DATA FRAME TO COMBINE THE ABOVE TWO #
data<-data.frame(group, x);

# CALCULATE SUMMARY FOR X #
x.mean<-tapply(data$x, data$group, mean, na.rm = T);
x.sum<-tapply(data$x, data$group, sum, na.rm = T);

# CREATE A DATA FRAME TO COMBINE SUMMARIES #
summ<-data.frame(x.mean, x.sum, group = names(x.mean));

# COMBINE DATA AND SUMMARIES TOGETHER #
combine<-merge(data, summ, by = "group");

OUTPUT DATA BASED ON ARBITRARY VALUES OF INDEX VARIABLE IN SAS

2005-12-09T23:02:00.000-05:00

***********************************************************
* OUTPUT DIFFERENT DATA SETS BASED ON ARBITRARY *
* VALUES OF ONE VARIABLE IN THE TABLE *
* DATE: 09-DEC-2005 *
***********************************************************;

/* SIMULATE SAMPLE DATA */
data one;
do i = 1 to 10;
do n = 1 to 5;
x = rannor(1);
output;
end;
end;
keep i x;
run;

/* CREATE A MACRO TO OUTPUT TABLES */
%macro slice(in =, out = , var = );
* in : name of input table *
* out: name of output table *
* var: variable to output tables *;

data data_&out;
set ∈
where &var = &out;
run;
%mend slice;

proc sort data = one;
by i;
run;

/* FINAL STEP TO OUTPUT TABLES */
data _null_;
set one;
by i;
if first.i then call execute ('%slice(in = one, out = '||i||', var = i)');
run;

***********************************************************
* END OF CODE *
***********************************************************;

ENTER DATA USING INPUT COMMEND IN STATA

2005-12-05T23:07:00.000-05:00

/*
EQUIVALENT SAS CODE FOR THE FOLLOWING STATA COMMANDS:
**************************************************
data person;
input name $ dept $;
datalines;
John Sales
Mary Acctng
;
run;

proc print data = person;
var name dept;
run;
**************************************************
*/

clear
cd c:\temp
log using mylog.log, replace
input str10 name str10 dept
John Sales
Mary Acctng
end
save person, replace
list dept name
log close
view mylog.log

HOW TO RENAME MANY VARIABLES IN SAS

2005-11-16T18:45:00.000-05:00

************************************************
* THIS CODE SHOWS HOW TO RENAME MANY VARIABLES *
* DATE: 11/16/2005 *
************************************************;

data one;
input a b c d e f g h i j;
cards;
1 2 3 4 5 6 7 8 9 10
;
run;

ods listing close;
ods output position = list;
proc contents data = one varnum;
run;
ods listing;

proc sql noprint;
select put(max(num), 2.) into :num from list;
select variable into :var1-:var&num from list;
select "rename_"||variable into :new1-:new&num from list;
quit;

%macro rename;

proc datasets lib = work;
modify one;
rename
%do i = 1 %to &num.;
&&var&i = &&new&i.
%end;
;
run;
quit;

%mend rename;

%rename;

*************************************************
* END OF CODE *
*************************************************;

READ MULTIPLE EXCEL FILES IN SAS USING MACRO

2005-09-29T12:15:00.000-04:00

*************************************************
* READ ALL EXCEL FILES WITH SAME STRUCTURE *
* FROM A FOLDER INTO SAS USING MACRO *
* DATE: SEP-29, 2005 *
*************************************************;

%macro ReadXls(dir = , out = );
*************************************************
* MACRO PARAMETERS: *
* DIR: FOLDER WHERE DATA IS STORED *
* OUT: NAME OF OUTPUT DATA TABLE *
*************************************************;

/* DELETE TABLE WITH SAME NAME AS OUTPUT DATA */
proc datasets;
delete &out;
run;
quit;

/* MANIPULATION THE STRING OF FOLDER PATH */
data _null_;
dir1 = '"'||symget('dir')||'\"';
dir21 = "'dir "||tranwrd(dir1, '\"', '\*.xls"');
dir22 = " /b'";
dir2 = compbl(dir21||dir22);
/* PASS THE STRINGS TO MACRO VARIABLES */
call symput('dir1', dir1);
call symput('dir2', dir2);
run;

/* DEFINE A PIPE */
filename Excel pipe &dir2;

/* GET A LIST OF FILES IN THE FOLDER */
data File;
infile Excel truncover end = last;
input Name $20.;
Path = compbl(&dir1||Name);
i + 1;
index = compress('file'||put(i, 2.), ' ');
/* GET THE NUMBER OF FILES */
if last then call symput('last', put(i, 2.));
/* PASS THE FILES PATH TO MACRO VARIABLES */
call symput(index, Path);
drop Name i;
run;

/* LOOP THROUGH THE FILES */
%do i = 1 %to &Last;
%put &&file&i;
/* READ DATA FROM EXCEL FILES */
proc import datafile = "&&file&i" out = temp1 dbms = excel2000 replace;
range = 'Sheet1$';
getnames = yes;
run;
/* ADD A FIELD FOR IMPORTED FILE NAME */
data temp2;
set temp1;
from = "&&file&i";
run;
/* APPEND DATA FROM EACH EXCEL TO A MAIN TABLE */
%if &i = 1 %then %do;
data &out;
set temp2;
run;
%end;
%else %do;
data &out;
set &out temp2;
run;
%end;
%end;

%mend ReadXls;
/* END OF MACRO */

/* CALL THE MACRO */
%ReadXls(dir = C:\temp, out = fromExcel);

*************************************************
* END OF CODE *
*************************************************;

READ MULTIPLE FILES USING PIPE IN SAS

2005-09-28T16:28:00.000-04:00

*************************************************
* READ ALL FILES IN A FOLDER INTO SAS *
* USING OPERATING SYSTEM PIPE *
* DATE: 28-SEP, 2005 *
*************************************************;

/*
SAMPLE DATA IN 4 FILES WITH UNKNOWN NAMES
12011997 M 09/08/04
12011997 F 01/26/01
...
*/

/* USE PIPE TO RETURN A LIST OF FILENAMES */
filename indata pipe 'dir "C:\temp" /b';

data File Data;
format path $100.;
/* OPEN A PIPE FOR INPUT */
infile indata truncover;
/* INPUT FILE NAMES */
input file $20.;
/* SPECIFY FULL PATH OF FILES */
path = 'C:\temp\'||file;
put path;

/* OPEN EACH FILE */
infile dummy filevar = path end = done truncover;
do while(not done);
/* INPUT DATA IN THE FILE */
input Account $ 1-8 Sex $ 10-10 Date mmddyy8.;
format Date Date.;
output;
end;
run;

*************************************************
* END OF CODE *
*************************************************;

DIRECT SAS OUTPUT TO LOG WINDOW

2005-09-26T18:12:00.000-04:00

*************************************************
* WRITE SAS OUTPUT TO LOG WINDOW INSTEAD OF *
* LIST WINDOW USING CATALOG *
* DATE: SEP-26, 2005 *
*************************************************;

options nodate pageno= 1;
data one (drop = n);
do i = 1 to 2;
do n = 1 to 10;
number = rannor(1);
output;
end;
end;
run;

/* ASSIGN AN OUTPUT FILE IN CATALOG */
filename OUT catalog 'work.catalog.out.output';

/* SEND OUTPUT TO FILE IN CATALOG */
proc printto print = OUT new;
run;

/* THINGS TO PRINT OUT */
proc print data = one;
var i number;
run;

proc means data = one n mean std;
var number;
class i;
run;

/* STOP SENDING OUTPUT */
proc printto;
run;

/* READ FILE IN CATALOG AND PUT IT IN LOG WINDOW */
data _null_;
infile OUT;
input;
put _infile_;
run;

*************************************************
* END OF CODE *
*************************************************;

CLASSIFICATION MODEL USING PARTIAL LEAST SQUARES IN SAS

2005-09-18T17:22:00.000-04:00

*************************************************
* CLASSIFICATION OF IRIS DATA USING PROC PLS *
* 18-SEP, 2005 *
*************************************************;

data iris;
input SepalLength SepalWidth PetalLength PetalWidth Species @@;
Setosa = (Species = 1);
Versicolor = (Species = 2);
Virginica = (Species = 3);
datalines;
50 33 14 02 1 64 28 56 22 3 65 28 46 15 2 67 31 56 24 3
63 28 51 15 3 46 34 14 03 1 69 31 51 23 3 62 22 45 15 2
... ...
;
run;

/* SELECT THE BEST NUMBER OF FACTORS USING CROSS-VALIDATION */
proc pls data = iris
method = pls details cv = split(3) cvtest (seed = 1);
model Setosa Versicolor Virginica = SepalLength SepalWidth PetalLength PetalWidth;
run;

/* FINAL PLS MODEL OF IRIS CLASSIFICATION */
ods listing close;
/* OUTPUT LOADINGS OF FACTORS FOR IVs*/
ods output XLoadings = xloadings_temp1;
proc pls data = iris method = pls nfac = 3 details;
model Setosa Versicolor Virginica = SepalLength SepalWidth PetalLength PetalWidth;
output out = pred_temp
p = p_Setosa p_Versicolor p_Virginica
xscore = Xscore;
run;
ods listing;

/* CALCULATE PREDICTED SPECIES */
data pred_iris;
set pred_temp;
format p_Species $10.;
if p_Setosa = max(p_Setosa, p_Versicolor, p_Virginica)
then p_Species = 'Setosa';
else if p_Versicolor = max(p_Setosa, p_Versicolor, p_Virginica)
then p_Species = 'Versicolor';
else if p_Virginica = max(p_Setosa, p_Versicolor, p_Virginica)
then p_Species = 'Virginica';
run;

/* CALCULATE MISCLASSIFICATION RATE */
proc freq data = pred_iris;
tables Species * p_Species;
run;

/* PLOT LOADINGS OF FACTORS VS. IVs */
proc transpose data = xloadings_temp1
out =xloadings_temp2 (where = (_name_ ~= 'NumberOfFactors'));
run;

data xloadings;
set xloadings_temp2;
drop _label_;
rename col1=Factor1 col2=Factor2
col3=Factor3 _name_ = Variable;
run;

proc sort data = xloadings;
by Variable;
run;

/* PLOT FACTOR LOADINGS ACROSS VARIABLES */
goptions reset = global device = bmp border;
axis1 label = (font = courier "Loading")
order = (-0.6 to 1 by 0.2)
minor = none
value = (font = courier);
axis2 label = (font = courier "Variable")
minor = none
value = (font = courier);
symbol1 i = join c = red l = 1 v = dot h = 0.5;
symbol2 i = join c = green l = 1 v = dot h = 0.5;
symbol3 i = join c = blue l = 1 v = dot h = 0.5;
legend1 label = none cborder = black value = (font = courier);

title h = 2 c = red font = courier 'Factor Loadings Across Variables';
proc gplot data = xloadings;
plot (Factor1 Factor2 Factor3) * Variable
/ overlay legend = legend1 vaxis = axis1
haxis = axis2 frame cframe = ligr vref = 0;
run;
quit;

/* PLOT SPECIES ON FIRST 2 FACTORS */
goptions reset = global device = bmp border;
axis2 label = (font = courier '1st Factor')
value = (font = courier);
axis1 label = (font = courier '2nd Factor')
value = (font = courier);
symbol1 i = none c = green l = 1 v = circle h = 0.5;
symbol2 i = none c = red l = 1 v = dot h = 0.5;
symbol3 i = none c = blue l = 1 v = square h = 0.5;
legend1 label = none cborder = black value = (font = courier);

title h = 2 c = blue font = courier
'Predicted Species on First 2 Factors';
proc gplot data = Pred_iris;
plot Xscore2 * Xscore1 = p_Species
/ vaxis = axis1 haxis = axis2 legend = legend1;
run;
quit;

*************************************************
* END OF CODE *
*************************************************;

CREATE DIAGLOG IN SAS USING SCL

2005-09-18T01:03:00.000-04:00

/* SCL PROGRAM TO SELECT A FILE BY DIAGLOG */
/* AND TO READ THE SELECTED FILE INTO SAS */
/* 18-SEP, 2005 */
/* SPECIAL THANKS TO Randy Herbison at westat */

/* DECLARE VARIABLES */
dcl char(200) InFile;
dcl num rc;

init:
/* CREATES A DIAGLOG FOR FILE SELECTION */
rc = filedialog('OPEN', InFile, ' ', ' ', '*.xls');

/* SELECTION BASED ON RETURNED VALUE OF rc */
select(rc);
when(0) put 'File selected';
when(-1) put 'Selection cancel'; return;
otherwise put 'ERROR occurred'; return;
end;

/* RUN PROCEDURE TO IMPORT DATA */
submit immediate;
proc import datafile = "&InFile"
out = work.hhc1 dbms = EXCEL replace;
getnames = no;
mixed = yes;
range = "a2:s10000";
run;
endsubmit;

return;
/* END OF PROGRAM */

CONVERT WIDE TO LONG TABLE IN SAS

2005-08-24T08:32:00.000-04:00

*************************************************
* TWO WAYS TO CONVERT WIDE TO LONG TABLE IN SAS *
* 19-AUG, 2005                                  *
*************************************************;

data wide;
do id = 1 to 2;
  array visit_[5];
  do i = 1 to 5;
    visit_[i] = rannor(i);
  end;
  output;
end;
drop i;
run;

*************************************************
* METHOD 1: PROC TRANSPOSE                      *
*************************************************;
proc sort data = wide;
  by id;
run;

proc transpose data = wide name = visit
  out = long1 (rename = (col1 = x));
  by id;
run;

*************************************************
* METHOD 2: ARRAY                               *
*************************************************;
data long2;
  set wide;
  array v[5] visit_1-visit_5;
  do i = 1 to dim(v);
    visit = vname(v[i]);
    x = v[i];
    output;
  end;
  drop visit_1-visit_5 i;
run;

*************************************************
* END OF CODE                                   *
*************************************************;

SUMMARIZE DATA BY GROUP IN SAS

2005-08-22T21:17:00.000-04:00

*************************************************
* THREE WAYS TO SUMMARIZE DATA BY GROUP *
* DATE: Aug-22, 2005 *
*************************************************;

data table;
do group = 1 to 3;
do i = 1 to 10;
x = rannor(1);
output;
end;
end;
drop i;
run;

*************************************************
* METHOD 1: PROC MEANS *
*************************************************;
proc means data = table noprint nway;
class group;
output out = summ1 (drop = _type_ rename = (_freq_ = x_count))
mean(x) = x_mean sum(x) = x_sum;
run;

*************************************************
* METHOD 2: PROC SQL *
*************************************************;
proc sql;
create table summ2 as
select
group,
count(x) as x_count,
mean(x) as x_mean,
sum(x) as x_sum
from
table
group by
group;
quit;

*************************************************
* METHOD 3: PROC SUMMARY *
*************************************************;
proc summary data = table nway;
class group;
output out = summ3 (drop = _type_ rename = (_freq_ = x_count))
mean(x) = x_mean sum(x) = x_sum;
run;

*************************************************
* END OF CODE *
*************************************************;

CONVERT LONG TO WIDE TABLE IN SAS

2005-08-19T22:13:00.000-04:00

***********************************************************
* THREE WAYS TO CONVERT LONG TABLE TO WIDE TABLE IN SAS *
* DATE : AUG-19, 2005 *
***********************************************************;

data long;
input id time x @@;
datalines;
1 1 123 1 2 126 1 3 128 2 1 112 2 2 110 2 3 115
3 1 223 3 2 226 3 3 228 4 1 212 4 2 210 4 3 215
;
run;

*************************************************
* METHOD 1: PROC TRANSPOSE *
*************************************************;
proc sort data = long;
by id;
run;

proc transpose data = long prefix = x
out = wide1 (drop = _name_);
id time;
by id;
run;

*************************************************
* METHOD 2: PROC SQL *
*************************************************;
proc sql;
create table wide2 as
select
id,
sum(case when time = 1 then x else 0 end) as x1,
sum(case when time = 2 then x else 0 end) as x2,
sum(case when time = 3 then x else 0 end) as x3
from
long
group by
id;
quit;

*************************************************
* METHOD 3: DATA STEP *
*************************************************;
data wide3;
retain id;
array u[*] x1-x3;
do i=1 to dim(u);
set long;
u[i]=x;
end;
drop x time i;
run;

*************************************************
* END OF CODE *
*************************************************;

CALL SAS FROM VB USING OLE

2005-08-18T09:13:00.000-04:00

'**********************************************************
'* CALL A SAS SESSION FROM VB USING OLE *
'* DATE : AUG-18, 2005 *
'**********************************************************

Option Compare Database
Option Explicit

Private Sub Button_Click()

' DECLARE SAS OBJECT
Dim OleSAS As Object

' INVOKE SAS APPLICATION
Set OleSAS = CreateObject("SAS.Application")
OleSAS.Wait = True
' LET SAS SESSION VISIBLE
OleSAS.Visible = True
' ASSIGN WORKING DIRECTORY TO SAS MACRO VARIABLE
OleSAS.Submit ("%let path = " & CurrentProject.Path &amp;amp;amp; ";")
' RUN A SAS CODE IN WORKING DIRECTORY
OleSAS.Submit ("%include '&path.\SAS_code.sas';")
' QUIT SAS SESSION
OleSAS.Quit
' FREE UP SAS OJBECT
Set OleSAS = Nothing
' GIVE A MESSAGE BOX
MsgBox "SAS job finished!"

End Sub

'**********************************************************
'* END OF CODE *
'**********************************************************

GAM MODELING WITH GAM & MGCV PACKAGES IN R

2005-08-14T23:30:00.000-04:00

###########################################################
# A DEMO OF CLASSIFICATION USING GAM #
# DEVELOPED BY Hastie and Tibshirani (1990) #
# DATE: 14-AUG-2005 #
###########################################################

library(gam);
data(kyphosis);

###########################################################
# A STEPWISE PROCEDURE TO SELECT BEST GAM BASED ON AIC #
###########################################################
ky.gam1<-gam(Kyphosis~Age+Number+Start, data=kyphosis, family=binomial);
ky.step<-step.gam(ky.gam1, scope=list("Age"=~1+Age+s(Age, 3)+s(Age, 4)+s(Age, 5), "Number"=~1+Number+s(Number, 3)+s(Number, 4), "Start"=~1+Start+s(Start, 3)+s(Start, 4)+s(Start, 5)), direction="both");
detach(package:gam);

###########################################################
# BUILD A GAM BASED ON THE OUTPUT OF STEPWISE PROCEDURE # ###########################################################
library(mgcv); ky.gam2<-gam(Kyphosis~s(Age, k=3, fx=T)+ Number+s(Start, k=3, fx=T), data=kyphosis, family=binomial);
summary(ky.gam2);

###########################################################
# VISUALIZATION OF THE ABOVE GAM MODEL # ###########################################################
plot(ky.gam2, all.terms=T, shade=T, shade.col=15, page=1);
title(main="Partial Dependences of Predictors"); vis.gam(ky.gam2, view=c("Age", "Start"), theta=60, phi =0,color="topo", main="3D Visualization of Kyphosis GAM model");
###########################################################
# END OF CODE #
###########################################################

BUILDING CART WITH RPART PACKAGE IN R

2005-08-11T19:15:00.000-04:00

###########################################################
# A DEMO OF PATTERN RECOGNITION USING CART #
# DEVELOPED BY Breiman, Friedman, Olshen, & Stone (1984) #
# DATE: 11-AUG-2005 #
###########################################################

data(iris);

###########################################################
# LOAD RPART AND MAPTREE PACKAGES #
###########################################################
library(rpart);
library(maptree);

###########################################################
# BUILD A INITIAL CART MODEL WITH IRIS DATA #
###########################################################
iris.tree1<-rpart(Species~., data = iris, control = rpart.control(minsplit = 5, xval=5));

###########################################################
# PLOT THE TREE DIAGRAM OF CART # ###########################################################
draw.tree(iris.tree1, nodeinfo=TRUE, pch=22, cex=1.2, size = 4);
title(main = "INITIAL TREE OF IRIS CART MODEL"); ###########################################################
# PLOT OF COST-COMPLEXITY PRUNING TO SELECT BEST SIZE #
###########################################################
plotcp(iris.tree1);
###########################################################
# PRUNE THE INITIAL CART TO THE ONE WITH BEST SIZE #
###########################################################
iris.tree2<-prune.Rpart(iris.tree1, best = 3);

###########################################################
# PLOT THE TREE DIAGRAM OF FINAL CART MODEL # ###########################################################
draw.tree(iris.tree2, nodeinfo=TRUE, pch=22, cex=1.2, size = 4);
title(main = "PRUNED TREE OF IRIS CART MODEL"); ###########################################################
# END OF CODE #
###########################################################

BUILDING CONDITIONAL TREE WITH PARTY PACKAGE IN R

2005-08-10T23:34:00.000-04:00

###########################################################
# A DEMO OF PATTERN RECOGNITION USING CONDITIONAL TREES #
# DEVELOPED BY Hothorn, Hornik & Zeileis (2004) #
# DATE: 10-AUG-2005 #
###########################################################

###########################################################
# LOAD IRIS DATA FROM R #
###########################################################
data(iris);

###########################################################
# LOAD PARTY PACKAGE #
###########################################################
library(party);

###########################################################
# BUILD A CONDITIONAL TREE MODEL WITH IRIS DATA #
###########################################################
iris.tree<-ctree(Species~., data = iris, controls = ctree_control(mincriterion = 0.95));

###########################################################
# PREDICT SPECIES USING TREE MODEL #
###########################################################
pred.Species<-predict(iris.tree);

###########################################################
# CALCULATE CONFUSION MATRIX #
###########################################################
attach(iris);
table(Species, pred.Species, deparse.level = 2);

###########################################################
# PLOT THE TREE DIAGRAM #
###########################################################
plot(iris.tree);

###########################################################
# END OF CODE #
###########################################################

RESHAPE BETWEEN LONG AND WIDE TABLES IN SAS

2005-08-08T16:28:00.000-04:00

*************************************************
* HOW TO RESHAPE TO LONG AND WIDE TABLES IN SAS *
* DATE: 08-AUG-2005 *
*************************************************
* THIS CODE MIGHT BE HELPFUL FOR THE ANALYSIS *
* OF REPEATED MEASURES FOR CLINICAL TRIAL *
*************************************************;

*************************************************
* GENERATE A LONG TABLE *
*************************************************;
data long;
do i = 1 to 5;
id = put(i, z3.);
do j = 1 to 10;
visit = j;
test = rannor(123);
output;
end;
end;
drop i j;
run;

*************************************************
* CONVERT THE LONG TABLE TO A WIDE ONE *
*************************************************;
proc sort data = long;
by id;
run;

proc transpose data = long prefix = test
out = wide (drop = _name_);
id visit;
by id;
run;

*************************************************
* CONVERT THE WIDE TABLE BACK TO A LONG ONE *
*************************************************;
data long2;
set wide;
array t{*} test1 - test10;
do i = 1 to 10;
visit = i;
test = t{i};
output;
end;
keep id visit test;
run;

*************************************************
* END OF CODE *
*************************************************;

INSERT CHART & DATA FROM SAS INTO EXCEL USING DDE

2005-08-03T18:15:00.000-04:00

***********************************************************
* A DEMO SHOWS HOW TO CREATE & OUTPUT A CHART IN SAS *
* AND INSERT IT INTO EXCEL USING DDE *
* DATE: 03-AUG-2005 *
***********************************************************;

data one;
do x = 1 to 10;
y = x*x + rannor(123)*5;
output;
end;
run;

filename plot 'C:\temp\plot.bmp';
goptions reset = global gsfname = plot gsfmode = replace cback = white device = emf
xmax = 10 in hsize = 8.4 in ymax = 7 in vsize = 5.4 in;

symbol i = spline w = 2 c = blue cv = red h = 1 v = dot;

title height = 1.5 font = arial color = red box = 2
'A DEMO HOW TO EXPORT CHART FROM SAS AND PUT IT IN EXCEL';

proc gplot data = one;
plot y * x;
run;
title;
quit;

options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office11\excel.exe"';

filename cmd dde 'excel|system';
filename label dde 'excel|sheet1!r1c1:r1c10';
filename data dde 'excel|sheet1!r2c1:r100c10';

data _null_;
file cmd;
put '[file.close(false)]';
put '[new(1)]';
put '[error(false)]';
put '[save.as("C:\temp\demo")]';
put '[app.maximize()]';
put '[workbook.insert(2)]';
put '[insert.picture("C:\temp\plot.bmp", 1)]';
run;

data _null_;
set one;
file label notab;
put 'X' '09'x 'Y';
file data notab;
put x 4.0 '09'x y 8.2;
run;

***********************************************************
* END OF CODE *
***********************************************************;

READ AN IMAGE INTO SAS

2005-08-03T00:34:00.000-04:00

**********************************************
* A DEMO SHOWS HOW TO READ AN IMAGE INTO SAS *
* DATE: 02-AUG-2005 *
**********************************************;

data image;
length function $ 8 style $ 8;
function = 'move';
xsys = '3'; ysys = '3';
x = 10; y = 10; output;
function = 'image';
imgpath = 'C:\temp\joyce.jpg';
x = 85; y = 85; style = 'fit'; output;
run;

title height = 3
color = red
underlin = 3
font = zapfb
"Joyce's Photo Read into SAS";

proc gslide anno = image;
run;
quit;

**********************************************
* END OF CODE *
**********************************************;

CONNECT TO ORACLE AND SQL SERVER FROM SAS USING OLEDB

2005-08-01T22:46:00.000-04:00

***********************************************************
* CONNECT TO ORACLE AND SQL SERVER DATABASE *
* FROM SAS USING OLEDB *
* DATE : AUG-01, 2005 *
***********************************************************;

***********************************************************
* 1. CONNECT TO ORACLE DATABASE *
***********************************************************;
libname OracleOLE oledb
provider = MSDAORA
dsn = ServerName
pwd = PassWord
uid = UserName
schema = SchemaName;

***********************************************************
* 2. CONNECT TO SQL SERVER DATABASE *
***********************************************************;
libname SqlOLE oledb
provider = SQLOLEDB.1
dsn = ServerName
pwd = PassWord
uid = UserName
schema = SchemaName;

***********************************************************
* END OF CODE *
***********************************************************;

READ DATA FROM EXCEL & ACCESS INTO R

2005-07-30T19:33:00.000-04:00

###########################################################
# WHAT IS R LANGUAGE? #
# R (www.r-project.org) is a free software environment #
# for statistical computing and graphics. #
###########################################################

###########################################################
# HOW TO READ DATA FROM EXCEL & ACCESS INTO R #
# DATE : JUL-30, 2005 #
###########################################################

library(RODBC);

###########################################################
# 1. READ DATA FROM EXCEL INTO R #
###########################################################
xlsConnect<-odbcConnectExcel("C:\\temp\\demo.xls");
demo<-sqlFetch(xlsConnect, "Sheet1");
odbcClose(xlsConnect);
rm(demo);

###########################################################
# 2. READ DATA FROM ACCESS INTO R #
###########################################################
mdbConnect<-odbcConnectAccess("C:\\temp\\demo.mdb");
sqlTables(mdbConnect);
demo<-sqlFetch(mdbConnect, "tblDemo");
odbcClose(mdbConnect);
rm(demo);

###########################################################
# END OF CODE #
###########################################################

READ DATA FROM ACCESS DATABASE INTO SAS

2005-07-30T00:42:00.000-04:00

***********************************************************
* METHODS TO READ DATA FROM ACCESS DATABASE INTO SAS *
* DATE : JUL-30, 2005 *
***********************************************************;

***********************************************************
* METHOD 1: OLEDB (SAS/ACCESS INTERFACE REQUIRED) *
***********************************************************;
libname OLEmdb oledb
init_string="Provider = Microsoft.Jet.OLEDB.4.0;
Data Source= c:\temp\demo.mdb";

data demo;
set OLEmdb.tblDemo;
run;

libname _all_ clear;

***********************************************************
* METHOD 2: PROC IMPORT *
***********************************************************;
proc import table = "tblDemo"
out = demo
dbms = access;
database="c:\temp\demo.mdb";
run;

***********************************************************
* METHOD 3: ODBC (ODBC DRIVER REQUIRED) *
***********************************************************;
libname ODBCmdb odbc
complete = "DSN=MS Access Database; DBQ=C:\temp\demo.mdb";

data demo;
set ODBCmdb.tblDemo;
run;

libname _all_ clear;

***********************************************************
* END OF CODE *
***********************************************************;

READ DATA FROM EXCEL INTO SAS

2005-07-28T11:57:00.000-04:00

***********************************************************
* METHODS TO READ DATA FROM EXCEL INTO SAS *
* DATE : JUL-28, 2005 *
***********************************************************;

***********************************************************
* METHOD 1: OLEDB (SAS/ACCESS INTERFACE REQUIRED) *
***********************************************************;
libname OLExls oledb
provider = "Microsoft.Jet.OLEDB.4.0"
properties=("data source" = 'c:\temp\demo.xls')
provider_string = "Excel 8.0";

data demo;
set OLExls."Sheet1$"n;
run;

libname _all_ clear;

***********************************************************
* METHOD 2: PROC IMPORT *
***********************************************************;
proc import datafile = "c:\temp\demo.xls"
out = demo;
sheet = "Sheet1$";
getnames = YES;
run;

***********************************************************
* METHOD 3: DDE (DYNAMIC DATA EXCHANGE) *
***********************************************************;
options noxwait noxsync;
x '"c:\program files\microsoft office\office11\excel"';

data _null_;
x=sleep(1);
run;

filename cmd dde "excel|system";
data _null_;
file cmd;
put '[app.minimize()]';
put '[open("C:\temp\demo.xls")]';
run;

filename DDExls dde "excel|sheet1!r2c1:r200c2";
data demo;
infile DDExls;
input x y $;
run;

data _null_;
file cmd;
put '[quit()]';
run;

***********************************************************
* METHOD 4: ODBC (ODBC DRIVER REQUIRED) *
***********************************************************;
proc sql;
connect to ODBC ("DSN=Excel Files;DBQ=C:\temp\demo.xls");
create table demo as
select * from connection to ODBC
(
select * from "Sheet1$"n
);
quit;

***********************************************************
* END OF CODE *
***********************************************************;