Monday, November 3, 2014
Sunday, December 15, 2013
Using proc cimport and proc cport
Warning: don't use Proc Cport to generate SAS transport file for submitting the data to FDA. See my article "Submit the Clinical Trial Datasets to FDA: Using the right .xpt file format"
Proc CPORT to creat SAS transport file
***This program will transfer all SAS data sets under c:\sourcedata directory ***to a single .xpt file called test.xtp; filename xptfile "c:\temp\test.xpt"; libname source "c:\sourcedata\"; ***For converting a data set; proc cport library=source file=xptfile memtype=data; run; ***For converting a format catalog; proc cport library=source file=xptfile memtype=catalog; run;
Proc CIMPORT to extract SAS transport file
/********************************************************************* /* This program is used for extract the data from SAS transport file /* generated by using PROC CPORT /*********************************************************************; FILENAME xpt 'transport_file_name.xpt'; libname dest 'destination_directory_name'; PROC CIMPORT INFILE= xpt LIBRARY=Dest; RUN;
Read SAS Transport file in .cpt or .dat format
READ .cpt file
* assign filename "in" to CPORT file "winners.cpt"; filename in "winners.cpt"; libname out '/work/'; * read from "winners.cpt" and save in library "out"; proc cimport file=in library=out; run; * use proc contents, proc print, and proc means to verify ; * that out.winners was created properly; * change winners to the name of your data member; proc contents data=out.winners; run; proc print data=out.winners(obs=10); run; proc means data=out.winners; run;
READ .dat file
* Using PROC CPORT, you can create a transport file in .dat extension. You can use the code below and modify to where you store the .dat file; libname outlib 'C:\TEMP'; filename infile 'TAL050003XF.dat'; proc cimport library=outlib file=infile; run;
Forest plot macro
This is from SAS example on Forest plot macro
%let mygpath=C:\temp;
%let macroLoc=C:\temp;
/*--Set up data set--*/
data forest;
input StudyName $1-16 GroupId or lcl ucl wt N Var ZeroWt;
label or='OR' lcl='LCL' ucl='UCL' wt='WT';
format wt percent6.1;
datalines;
Modano (1967) 1 0.590 0.096 3.634 1 100 0.1 .
Borodan (1981) 1 0.464 0.201 1.074 3.5 300 0.1 0
Leighton (1972) 1 0.394 0.076 2.055 2 200 0.1 0
Novak (1992) 1 0.490 0.088 2.737 2 200 0.1 .
Stawer (1998) 1 1.250 0.479 3.261 3 300 0.1 .
Truark (2002) 1 0.129 0.027 0.605 2.5 250 0.1 .
Fayney (2005) 1 0.313 0.054 1.805 2 200 0.1 .
Intermediate 2 0.328 0.233 0.462 . . . .
Modano (1969) 1 0.429 0.070 2.620 2 200 0.1 .
Soloway (2000) 1 0.718 0.237 2.179 3 300 0.1 .
Adams (1999) 1 0.143 0.082 0.250 4 400 0.1 .
Truark2 (2002) 1 0.129 0.027 0.605 2.5 250 0.1 .
Fayney2 (2005) 1 0.313 0.054 1.805 2 200 0.1 .
Modano2 (1969) 1 0.429 0.070 2.620 2 200 0.1 .
Soloway2(2000) 1 0.718 0.237 2.179 3 300 0.1 .
Adams2 (1999) 1 0.143 0.082 0.250 4 400 0.1 .
Overall 3 0.328 0.233 0.462 . . . .
;
run;
options sasautos=("¯oLoc", sasautos);
options mautosource mprint mlogic;
ods listing close;
ods html image_dpi=100 style=listing path="&mygpath" file='forestplots.html';
ods graphics / reset width=600px height=400px;
/*--Forest Plot with common options--*/
%ForestMacro(data=forest, Study=StudyName, Group=GroupId, OddsRatio=or, LCL=lcl, UCL=ucl,
width=6.5in, Weight=wt, Bands=YES, GraphWalls=YES, DisplayCols=yes);
/*--Forest Plot with minimum options--*/
%ForestMacro(data=forest, Study=StudyName, Group=GroupId, OddsRatio=or, LCL=lcl, UCL=ucl);
/*--Forest Plot with additional statistics columns--*/
%ForestMacro(data=forest, Study=StudyName, Group=GroupId, OddsRatio=OR, LCL=lcl, UCL=ucl,
StatCol1=N, StatCol2=Var, Bands=NO, GraphWalls=NO, StatWalls=NO, Borders=NO);
/*--Forest Plot with ONLY additional statistics columns--*/
%ForestMacro(data=forest, Study=StudyName, Group=GroupId, OddsRatio=OR, LCL=lcl, UCL=ucl,
Weight=wt, DisplayCols=N, StatCol1=N, StatCol2=Var, StatCol3=OR, StatCol4=wt, GraphWalls=Yep, Borders=);
data ForestNoGroups;
set Forest (where=(GroupId=1));
drop GroupId;
run;
/*--Forest Plot with no statistics columns--*/
%ForestMacro(data=ForestNoGroups, Study=StudyName, OddsRatio=OR, LCL=lcl, UCL=ucl,
DisplayCols=YES, Weight=wt, GraphWalls=YES, StatWalls=yes, Borders=NO);
/*--Forest Plot with no statistics columns--*/
%ForestMacro(data=ForestNoGroups, Study=StudyName, OddsRatio=OR, LCL=lcl, UCL=ucl,
DisplayCols=YES, GraphWalls=YES, StatWalls=yes, Borders=NO);
/*--Set up data set--*/
data forest2;
input StudyName $1-16 GroupId or lcl ucl wt N Var ZeroWt;
label or='OR' lcl='LCL' ucl='UCL' wt='WT';
format wt percent6.1;
datalines;
Modano (1967) 1 0.590 0.096 3.634 1 100 0.1 .
Borodan (1981) 1 0.464 0.201 1.074 3.5 300 0.1 0
Fayney (2005) 1 0.313 0.054 1.805 2 200 0.1 .
Intermediate 2 0.328 0.233 0.462 . . . .
Modano (1969) 1 0.429 0.070 2.620 2 200 0.1 .
Soloway (2000) 1 0.718 0.237 2.179 3 300 0.1 .
Adams (1999) 1 0.143 0.082 0.250 4 400 0.1 .
Truark2 (2002) 1 0.129 0.027 0.605 2.5 250 0.1 .
Overall 3 0.328 0.233 0.462 . . . .
;
run;
/*--Forest Plot with common options--*/
%ForestMacro(data=forest2, Study=StudyName, Group=GroupId, OddsRatio=or, LCL=lcl, UCL=ucl,
Weight=wt, Bands=YES, GraphWalls=YES, DisplayCols=YES);
ods html close;
ods listing;Create a forest plot with the SGPLOT procedure
This is from SAS example
data forest;
input Study $1-16 grp OddsRatio LowerCL UpperCL Weight;
format weight percent5. Q1 Q3 4.2 oddsratio lowercl uppercl 5.3;
ObsId=_N_;
OR='OR'; LCL='LCL'; UCL='UCL'; WT='Weight';
if grp=1 then do;
weight=weight*.05;
Q1=OddsRatio-OddsRatio*weight;
Q3=OddsRatio+OddsRatio*weight;
lcl2=lowercl;
ucl2=uppercl;
end;
else study2=study;
datalines;
Modano (1967) 1 0.590 0.096 3.634 1
Borodan (1981) 1 0.464 0.201 1.074 3.5
Leighton (1972) 1 0.394 0.076 2.055 2
Novak (1992) 1 0.490 0.088 2.737 2
Stawer (1998) 1 1.250 0.479 3.261 3
Truark (2002) 1 0.129 0.027 0.605 2.5
Fayney (2005) 1 0.313 0.054 1.805 2
Modano (1969) 1 0.429 0.070 2.620 2
Soloway (2000) 1 0.718 0.237 2.179 3
Adams (1999) 1 0.143 0.082 0.250 4
Truark2 (2002) 1 0.129 0.027 0.605 2.5
Fayney2 (2005) 1 0.313 0.054 1.805 2
Modano2 (1969) 1 0.429 0.070 2.620 2
Soloway2(2000) 1 0.718 0.237 2.179 3
Adams2 (1999) 1 0.143 0.082 0.250 4
Overall 2 0.328 0.233 0.462 .
;
run;
proc sort data=forest out=forest2;
by descending obsid;
run;
/* Add sequence numbers to each observation */
data forest3;
set forest2 end=last;
retain fmtname 'Study' type 'n';
studyvalue=_n_;
if study2='Overall' then study2value=1;
else study2value = .;
/* Output values and formatted strings to data set */
label=study;
start=studyvalue;
end=studyvalue;
output;
if last then do;
hlo='O';
label='Other';
end;
run;
/* Create the format from the data set */
proc format library=work cntlin=forest3;
run;
/* Apply the format to the study values and remove Overall from Study column. */
/* Compute the width of the box proportional to weight in log scale. */
data forest4;
format studyvalue study2value study.;
drop fmtname type label start end hlo pct;
set forest3 (where=(studyvalue > 0)) nobs=nobs;
if studyvalue=1 then studyvalue=.;
/* Compute marker width */
x1=oddsratio / (10 ** (weight/2));
x2=oddsratio * (10 ** (weight/2));
/* Compute top and bottom offsets */
if _n_ = nobs then do;
pct=0.75/nobs;
call symputx("pct", pct);
call symputx("pct2", 2*pct);
call symputx("count", nobs);
end;
run;
ods listing close;
ods html image_dpi=100 path="." file='sgplotforest.html';
ods graphics / reset width=600px height=400px imagename="Forest_Plot_Vector" imagefmt=gif;
title "Impact of Treatment on Mortality by Study";
title2 h=8pt 'Odds Ratio and 95% CL';
proc sgplot data=forest4 noautolegend;
scatter y=study2value x=oddsratio / markerattrs=graphdata2(symbol=diamondfilled size=10);
scatter y=studyvalue x=oddsratio / xerrorupper=ucl2 xerrorlower=lcl2 markerattrs=graphdata1(symbol=squarefilled size=0);
vector x=x2 y=studyvalue / xorigin=x1 yorigin=studyvalue lineattrs=graphdata1(thickness=8) noarrowheads;
scatter y=studyvalue x=or / markerchar=oddsratio x2axis;
scatter y=studyvalue x=lcl / markerchar=lowercl x2axis;
scatter y=studyvalue x=ucl / markerchar=uppercl x2axis;
scatter y=studyvalue x=wt / markerchar=weight x2axis;
refline 1 100 / axis=x;
refline 0.1 10 / axis=x lineattrs=(pattern=shortdash) transparency=0.5;
inset ' Favors Treatment' / position=bottomleft;
inset 'Favors Placebo' / position=bottom;
xaxis type=log offsetmin=0 offsetmax=0.35 min=0.01 max=100 minor display=(nolabel) ;
x2axis offsetmin=0.7 display=(noticks nolabel);
yaxis display=(noticks nolabel) offsetmin=0.1 offsetmax=0.05 values=(1 to &count by 1);
run;
ods html close;
ods listing;
data forest;
input Study $1-16 grp OddsRatio LowerCL UpperCL Weight;
format weight percent5. Q1 Q3 4.2 oddsratio lowercl uppercl 5.3;
ObsId=_N_;
OR='OR'; LCL='LCL'; UCL='UCL'; WT='Weight';
if grp=1 then do;
weight=weight*.05;
Q1=OddsRatio-OddsRatio*weight;
Q3=OddsRatio+OddsRatio*weight;
lcl2=lowercl;
ucl2=uppercl;
end;
else study2=study;
datalines;
Modano (1967) 1 0.590 0.096 3.634 1
Borodan (1981) 1 0.464 0.201 1.074 3.5
Leighton (1972) 1 0.394 0.076 2.055 2
Novak (1992) 1 0.490 0.088 2.737 2
Stawer (1998) 1 1.250 0.479 3.261 3
Truark (2002) 1 0.129 0.027 0.605 2.5
Fayney (2005) 1 0.313 0.054 1.805 2
Modano (1969) 1 0.429 0.070 2.620 2
Soloway (2000) 1 0.718 0.237 2.179 3
Adams (1999) 1 0.143 0.082 0.250 4
Truark2 (2002) 1 0.129 0.027 0.605 2.5
Fayney2 (2005) 1 0.313 0.054 1.805 2
Modano2 (1969) 1 0.429 0.070 2.620 2
Soloway2(2000) 1 0.718 0.237 2.179 3
Adams2 (1999) 1 0.143 0.082 0.250 4
Overall 2 0.328 0.233 0.462 .
;
run;
proc sort data=forest out=forest2;
by descending obsid;
run;
/* Add sequence numbers to each observation */
data forest3;
set forest2 end=last;
retain fmtname 'Study' type 'n';
studyvalue=_n_;
if study2='Overall' then study2value=1;
else study2value = .;
/* Output values and formatted strings to data set */
label=study;
start=studyvalue;
end=studyvalue;
output;
if last then do;
hlo='O';
label='Other';
end;
run;
/* Create the format from the data set */
proc format library=work cntlin=forest3;
run;
/* Apply the format to the study values and remove Overall from Study column. */
/* Compute the width of the box proportional to weight in log scale. */
data forest4;
format studyvalue study2value study.;
drop fmtname type label start end hlo pct;
set forest3 (where=(studyvalue > 0)) nobs=nobs;
if studyvalue=1 then studyvalue=.;
/* Compute marker width */
x1=oddsratio / (10 ** (weight/2));
x2=oddsratio * (10 ** (weight/2));
/* Compute top and bottom offsets */
if _n_ = nobs then do;
pct=0.75/nobs;
call symputx("pct", pct);
call symputx("pct2", 2*pct);
call symputx("count", nobs);
end;
run;
ods listing close;
ods html image_dpi=100 path="." file='sgplotforest.html';
ods graphics / reset width=600px height=400px imagename="Forest_Plot_Vector" imagefmt=gif;
title "Impact of Treatment on Mortality by Study";
title2 h=8pt 'Odds Ratio and 95% CL';
proc sgplot data=forest4 noautolegend;
scatter y=study2value x=oddsratio / markerattrs=graphdata2(symbol=diamondfilled size=10);
scatter y=studyvalue x=oddsratio / xerrorupper=ucl2 xerrorlower=lcl2 markerattrs=graphdata1(symbol=squarefilled size=0);
vector x=x2 y=studyvalue / xorigin=x1 yorigin=studyvalue lineattrs=graphdata1(thickness=8) noarrowheads;
scatter y=studyvalue x=or / markerchar=oddsratio x2axis;
scatter y=studyvalue x=lcl / markerchar=lowercl x2axis;
scatter y=studyvalue x=ucl / markerchar=uppercl x2axis;
scatter y=studyvalue x=wt / markerchar=weight x2axis;
refline 1 100 / axis=x;
refline 0.1 10 / axis=x lineattrs=(pattern=shortdash) transparency=0.5;
inset ' Favors Treatment' / position=bottomleft;
inset 'Favors Placebo' / position=bottom;
xaxis type=log offsetmin=0 offsetmax=0.35 min=0.01 max=100 minor display=(nolabel) ;
x2axis offsetmin=0.7 display=(noticks nolabel);
yaxis display=(noticks nolabel) offsetmin=0.1 offsetmax=0.05 values=(1 to &count by 1);
run;
ods html close;
ods listing;
SAS codes for creating the forest plot for sub-group analyses
This is from the SAS blog by Sanjay Matange.
/*--SAS 9.3 release or later --*/
/*--Name: Sanjay Matange --*/
/*--Company: SAS Institute Inc. --*/
/*--Date : 06Aug 2012 --*/
/*--History: 06Aug2012/SNM - Add horizontal bands. --*/
/*-- 06Sep2012/SNM - Full Width horizontal bands. --*/
/*-- 07Jan2013/SNM - Use HighLow plot for subgroup with fonts --*/
/*-------------------------------------------------------------------------*/
/*--CTSPedia General AE G1 Forest Plot--*/
%let gpath='C:\';
ods html close;
/*--To retain leading and trailing blanks, we must use nbsp instead of blank--*/
/*--For visibility, we have used '.' in place of blanks --*/
/*-- Later these '.' values are changed to nbsp 'A0'x --*/
/*--Regular leading blanks will be stripped, losing the indentation --*/
/*--Add "Id" to identify subgroup headings from values --*/
data forest;
input Id Subgroup $3-27 Count Percent Mean Low High PCIGroup Group PValue;
zero=0; one=1;
PCI_lbl='PCI Group';
grp_lbl='Group';
pval_lbl='P Value';
ObsId=_n_;
if count ne . then CountPct=put(count, 4.0) || "(" || put(percent, 3.0) || ")";
datalines;
1 Overall..................2166 100 1.3 0.9 1.5 17.2 15.6 .
1 Age....................... . . . . . . 0.05
2 ..<= 65 Yr...............1534 71 1.5 1.05 1.9 17.0 13.2 .
2 ..> 65 Yr................ 632 29 0.8 0.6 1.25 17.8 21.3 .
1 Sex....................... . . . . . . 0.13
2 ..Male...................1690 78 1.5 1.05 1.9 16.8 13.5 .
2 ..Female................. 476 22 0.8 0.6 1.3 18.3 22.9 .
1 Race or ethnic group...... . . . . . . 0.52
2 ..Nonwhite............... 428 20 1.05 0.6 1.8 18.8 17.8 .
2 ..White..................1738 80 1.2 0.85 1.6 16.7 15.0 .
1 From MI to Randomization.. . . . . . . 0.81
2 ..<= 7 days.............. 963 44 1.2 0.8 1.5 18.9 18.6 .
2 ..> 7 days...............1203 56 1.15 0.75 1.5 15.9 12.9 .
1 Infract-related artery.... . . . . . . 0.38
2 ..LAD.................... 781 36 1.4 0.9 1.9 20.1 16.2 .
2 ..Other..................1385 64 1.1 0.8 1.4 15.6 15.3 .
1 Ejection Fraction......... . . . . . . 0.48
2 ..< 50%..................1151 54 1.2 0.8 1.5 22.6 20.4 .
2 ..>= 50%................. 999 46 0.9 0.6 1.4 10.7 11.1 .
1 Diabetes.................. . . . . . . 0.41
2 ..Yes.................... 446 21 1.4 0.9 2.0 29.3 23.3 .
2 ..No.....................1720 79 1.1 0.8 1.5 14.4 13.5 .
1 Killip class.............. . . . . . . 0.39
2 ..I......................1740 81 1.2 0.8 1.6 15.2 13.1 .
2 ..II-IV.................. 413 19 0.95 0.6 1.5 25.3 26.9 .
;
run;
ods listing;
/*proc print;run;*/
/*--Replace '.' in subgroup with blank--*/
data forest2;
set forest;
subgroup=translate(subgroup, ' ', '.');
val=mod(_N_-1, 6);
if val eq 1 or val eq 2 or val eq 3 then ref=obsid;
/*--Separate Subgroup headers and obs into separate columns--*/
if id=1 then do;
heading=subgroup;
subgroup='';
end;
run;
/*proc print;run;*/
/*--Create font with smaller fonts for axis label, value and data--*/
proc template;
define style listingSF;
parent = Styles.Listing;
style GraphFonts from GraphFonts
"Fonts used in graph styles" /
'GraphDataFont' = ("<sans-serif>, <MTsans-serif>",7pt)
'GraphValueFont' = ("<sans-serif>, <MTsans-serif>",7pt)
'GraphLabelFont' = ("<sans-serif>, <MTsans-serif>",7pt, bold);
;
end;
run;
%let dpi=150;
ods listing style=listingSF gpath=&gpath image_dpi=&dpi;
/*--Define templage for Forest Plot--*/
/*--Template uses a Layout Lattice of 5 columns--*/
proc template;
define statgraph Forest;
dynamic _bandcolor _headercolor _subgroupcolor;
begingraph;
layout lattice / columns=4 columnweights=(0.23 0.07 0.4 0.3);
/*--Column headers--*/
sidebar / align=top;
layout lattice / rows=2 columns=4 columnweights=(0.18 0.17 0.35 0.3)
backgroundcolor=_headercolor opaque=true;
entry textattrs=(size=8 weight=bold) halign=left "Subgroup";
entry textattrs=(size=8 weight=bold) " No.of Patients (%)";
entry textattrs=(size=8 weight=bold) "Hazard Ratio";
entry halign=center textattrs=(size=8 weight=bold) "4-Yr Cumulative Event Rate" ;
entry " ";
entry " ";
entry " ";
entry halign=center textattrs=(size=6) "Medical Therapy";
endlayout;
endsidebar;
/*--First Subgroup column, shows only the Y2 axis --*/
/*--Use HighLow plot to place the heading and subgroup values as HighLabels--*/
/*--Indenting is done by making the 2nd highlow bar 1 unit long --*/
/*--Highlow bar itself has thickness=0 --*/
layout overlay / walldisplay=none
xaxisopts=(display=none linearopts=(viewmin=0 viewmax=20))
yaxisopts=(reverse=true display=none tickvalueattrs=(weight=bold));
referenceline y=ref / lineattrs=(thickness=15 color=_bandcolor);
highlowplot y=obsid low=zero high=zero / highlabel=heading lineattrs=(thickness=0)
labelattrs=(size=7 weight=bold);
highlowplot y=obsid low=zero high=one / highlabel=subgroup lineattrs=(thickness=0);
endlayout;
/*--Second column showing Count and percent--*/
layout overlay / xaxisopts=(display=none)
yaxisopts=(reverse=true display=none) walldisplay=none;
referenceline y=ref / lineattrs=(thickness=15 color=_bandcolor);
scatterplot y=obsid x=zero / markercharacter=countpct
markercharacterattrs=graphvaluetext;
endlayout;
/*--Third column showing odds ratio graph--*/
layout overlay / xaxisopts=(label=' <---PCI Better---- ----Medical Therapy Better--->'
linearopts=(tickvaluepriority=true
tickvaluelist=(0.0 0.5 1.0 1.5 2.0 2.5)))
yaxisopts=(reverse=true display=none) walldisplay=none;
referenceline y=ref / lineattrs=(thickness=15 color=_bandcolor);
highlowplot y=obsid low=low high=high;
scatterplot y=obsid x=mean / markerattrs=(symbol=squarefilled);
referenceline x=1;
endlayout;
/*--Fourth column showing PCIGroup and Group columns--*/
layout overlay / x2axisopts=(display=(tickvalues) offsetmin=0.25 offsetmax=0.25)
yaxisopts=(reverse=true display=none) walldisplay=none;
referenceline y=ref / lineattrs=(thickness=15 color=_bandcolor);
scatterplot y=obsid x=pci_lbl / markercharacter=PCIGroup xaxis=x2
markercharacterattrs=graphvaluetext;
scatterplot y=obsid x=grp_lbl / markercharacter=group xaxis=x2
markercharacterattrs=graphvaluetext;
scatterplot y=obsid x=pval_lbl / markercharacter=pvalue xaxis=x2
markercharacterattrs=graphvaluetext;
endlayout;
endlayout;
entryfootnote halign=left textattrs=(size=7)
'The p-value is from the test statistic for testing the interaction between the '
'treatment and any subgroup variable';
endgraph;
end;
run;
/*--Render Forest Plot without horizontal bands--*/
ods graphics / reset width=7in height=5in imagename='Forest_HighLow_93';
proc sgrender data=Forest2 template=Forest;
dynamic _bandcolor='white' _headercolor='white';
run;
/*--Render Forest Plot with horizontal bands--*/
ods graphics / reset width=7in height=5in imagename='Forest_HighLow_Band_93';
proc sgrender data=Forest2 template=Forest;
dynamic _bandcolor='cxf0f0f0' _headercolor='cxd0d0d0';
run;
Subscribe to:
Posts (Atom)