Read only access to SAS Data sets
Compiled by Soumya Gopinath

Lock the entire SAS data library by using the option Access=Readonly.

Libname libref path access= Readonly;

Silly Proc Report !
Compiled by Soumya Gopinath

An annoying period (.) appeared in the p-value column when the break statement was applied in proc REPORT. How can we eliminate this period (.) from the report (rtf file)?

Solution:
Firstly, the data type of p-value variable is numeric then the break option will replace the missing values with a period. To avoid this situation change the data type of p-value variable to character. Then the missing values will be replaced by blank space only.
Eg:
/* Create dataset with 5 variables and 8 observations*/
data test;
input usubjid $ parameter $ visit $ trtgrp result;
datalines;
001 FEV1 VISIT1 1 2.34
002 FEV1 VISIT1 3 0.98
001 FEV1 VISIT4 1 2.04
002 FEV1 VISIT4 3 1.98
001 FVC VISIT1 1 2.34
002 FVC VISIT1 3 2.98
001 FVC VISIT4 1 2.44
002 FVC VISIT4 3 1.99
;
run;
/* Carrying out ANOVA*/
ods output ModelAnova= test1;
proc glm data=test;
by parameter visit;
class trtgrp;
model result=trtgrp/ss3;
lsmeans trtgrp/adjust=t pdiff;
run;

data test2;
set test1;
test=' '; /* dummy variable*/
keep parameter visit probf Test;
run;

/* Specify the output location*/

ods rtf file="D:\soumya\test.rtf" style=styles.listingstyle;

proc report data = test2 nowd spacing = 2 headline headskip split = '*' missing;
column parameter visit test,(probf);
define parameter / group order=data left 'Parameter' ;
define visit / display left 'Timepoint' ;
define Test / across center "Test" ;
define probf/ display left "p-value";
break after parameter/summarize suppress;
run;

ods _all_ close; /* Closing all ODS outputs statements*/

In the above example probf is a numeric variable within the across variable ‘test’. So in the output a period occurred when the break statement active. Eliminate this we add one more statement in the dataset Test2.
probf1=put (probf, pvalue6.); /* for converting numeric type to character*/

Generally we can say that the numeric variables within the across variable in REPORT procedure should make a period in the blank space and to avoid this convert numeric variables to character by using PUT function.

SAS with mySQL
Compiled by Soumya Gopinath

How to transfer the MySQL Databases named dbformo and dbformo_add to SAS datasets library names formoSQL and AddSQL respectively?

Solution:
libname libname ODBC datasrc= source user= user name password= password schema= database;

By using the procedure COPY we can copy the library formoSQL and AddSQL to our permanent SAS libraries FormoDM and FormoAdd respectively. Before executing the connection string add an ODBC Data source name in the current system, which should be the same as given in datasrc= option. The process,

1) Settings -> Control Panel ->Administrative Tools ->Data Source (ODBC)
2) Click on the Add button.
3) Select the driver for which you want to set up a data source (MySQL ODBC 3.51 Driver) and click Finish button.
4) Add required information in Connector/ODBC window. Click OK button. Then the process is completed.

VB and mySQL
Compiled by Soumya Gopinath

How will you connect a data entry application written in VB to mySQL ?

DRIVER= {MySQL ODBC 3.51 Driver};SERVER=server id; DATABASE=database name; UID=user name; PWD=password";

Wilcoxon Signed Rank test

Compiled by Sreedevi Menon

As part of the efficacy analysis of a recent Phase III study , it was planned to determine the significant difference in efficacy of treatments over the weeks. Each treatment group was analyzed separately. The paired t-test was employed to carry out the analysis on the parametric data.The Wilcoxon Signed Rank test was used for analysis of efficacy of treatments with respect to non-parametric data. The null hypothesis was that in the underlying population of differences among pairs the median difference is equal to 0. The alternate hypothesis may be one sided or two sided.

In SAS, this test is performed using the proc univariate procedure. Before applying the procedure, the difference between the value of parameter at the two time points that are to be compared (usually pre and post dose values) has to be computed. This difference is then defined in the “var” statement of the procedure. The syntax is as followed

ods output BasicMeasures= _A TestsforLocation= _B
proc univariate data= eff_data;
var diff;
run;
ods output close;

The output will contain p-value corresponding to Student’s t, Sign and Signed Rank tests. The p-value corresponding to the signed rank test will be considered. Further, For a 1-sided p-value, we would divide the 2-sided p-value by 2.The ‘ods output statement’ is used to extract descriptive statistics and the p value to two different datasets by using the as shown in the above example

Transpose macro
Compiled by Ajish K Mani

%macro trans(dsn=, outdsn=, vartran=, idtran=, bytran=, copytran=);
proc transpose data=&dsn out=&outdsn let;
var &vartran;
by &bytran;

%if &copytran ne %then %do;
copy ©tran;
%end;

%if &idtran ne %then %do;
id &idtran;
%end;
run;
%mend trans;

data input
input patno 1 sex $ 3 visit 5 weight 7-8 height 10-12;
datalines;
1 m 1 80 150
2 f 1 65 160
3 f 1 70 165
4 f 1 60 170
5 m 1 68 168
;
run;

proc sort data=input out=output;
by patno;
run;


%trans (dsn=output, outdsn=transpose(rename=(col1=Result)), vartran=weight height, bytran=patno);

data transpose;
set transpose;
label _name_ ="Parameter" ;
rename _name_=Parameter;
run;