SAS - Gplot – A simple example

•SAS/ Graph modular is feathered by the flexible PROC gplot

•A simple example:

proc gplot data=sashelp.Class;

symbol i=none v=star;

plot height*weight;

run;

quit;

Resulting graph

SAS - PROC tabulate – For example 2

proc tabulate data=sashelp.Class;

class sex;

var height weight age;

table sex all, (age height weight)*(std mean sum);

run;

Result:

SAS - PROC tabulate – example (1)

proc tabulate data=sashelp.Class;

class sex;

var height weight;

table sex, height weight;

run;

Result:

  

SAS - PROC tabulate

•Summarize the data in the form of a well organized table

•Syntax:

PROC tabulate DATA=dataname;

ClASS class variables;

VAR variables;

TABLE page, row, column description/options;

 RUN;

Subqueries Operators SQL

 data plan_2008;
 input plancode $ year plan $ cust;
 cards;
 p101 2008 1min/2rs 450
 p201 2008 2min/3rs 560
 p207 2008 1min/1ps 660
 p345 2008 2min/2rs 640
 p420 2008 1min/80p 713
 ;
data plan_2009;
input plancode $ year plan $ cust;
cards;
p109 2009 1sec/2p 780
p207 2009 1min/1rs 640
p225 2009 1sec/1p 680
p420 2009 1min/80p 750
p431 2009 2sec/1p 790
;
data ae;
input pid visit $ aetype $;
cards;
103 visit1 eye dis
125 visit1 ear dis
145 visit1 eye dis
178 visit1 ear dis
198 visit1 rashes
103 visit2 ear dis
113 visit2 ear dis
125 visit2 eye dis
178 visit2 rashes
198 visit2 ear dis
103 visit3 rashes
113 visit3 eye dis
178 visit3 rashes
210 visit3 ear dis
;

/*row comparision between subqueries and operators*/
/*in all visits*/
proc sql;
select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
intersect
select pid from ae where visit='visit3';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
intersect
select pid from ae where visit='visit3');
quit;

/*1st visit*/
proc sql;
select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3');
quit;

/*1st & 2nd visit*/
proc sql;
select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3');
quit;

/*3rd visit*/
proc sql;
select pid from ae where visit='visit3'
except
select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit3'
except
select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2');
quit;

SAS - Operators SQL

 data plan_2008;
 input plancode $ year plan $ cust;
 cards;
 p101 2008 1min/2rs 450
 p201 2008 2min/3rs 560
 p207 2008 1min/1ps 660
 p345 2008 2min/2rs 640
 p420 2008 1min/80p 713
 ;

data plan_2009;
input plancode $ year plan $ cust;
cards;
p109 2009 1sec/2p 780
p207 2009 1min/1rs 640
p225 2009 1sec/1p 680
p420 2009 1min/80p 750
p431 2009 2sec/1p 790
;

/*operators*/
proc sql;
select * from plan_2008
union all
select * from plan_2009;
quit;

proc sql;
select * from plan_2008
union
select * from plan_2009;
quit;

proc sql;
select plancode,plan from plan_2008
intersect
select plancode,plan from plan_2009;
quit;

/*customisation*/
proc sql;
select plancode,plan,
'available in 2008-2009'as message from plan_2008
intersect
select plancode,plan,
'available in 2008-2009'as message from plan_2009;
quit;

/*except*/
proc sql;
select plancode,plan from plan_2008
except
select plancode,plan from plan_2009;
quit;

/*customization*/
proc sql;
select plancode,plan,
'only in 2008 'as message from plan_2008
except
select plancode,plan,
'only in 2008'as message from plan_2009;
quit;

proc sql;
proc sql;
select plancode,plan,
'only in 2008 'as message from plan_2008
except
select plancode,plan,
'only in 2008'as message from plan_2009;
quit;

proc sql;
(select plancode,plan from plan_2008
except
select plancode,plan from plan_2009)
union all
(select plancode,plan from plan_2009
except
select plancode,plan from plan_2008);
quit;

proc sql;
(select plancode,plan,
'only in 2008 'as message from plan_2008
except
select plancode,plan,
'only in 2008'as message from plan_2009)
union all
(select plancode,plan,
'only in 2009 'as message from plan_2008
except
select plancode,plan,
'only in 2009'as message from plan_2009);
quit;

/*sub queries*/
proc sql;
select * from plan_2008 where plancode not in(select plancode from plan_2009)
union all
select * from plan_2009 where plancode not in(select plancode from plan_2008);
quit;

proc sql;
select * from plan_2008 where plancode  in(select plancode from plan_2009)
union all
select * from plan_2009 where plancode  in(select plancode from plan_2008);
quit;

proc sql;
select * from plan_2009 where plancode  in(select plancode from plan_2008)
select * from plan_2008  where (cust<plan_2009 ,cust);
quit;

SAS - Strings SQL

proc import datafile='C:\Documents and Settings\mobileclub\Desktop\source\xls\data.xls'
out=demo1 dbms=excel replace;
sheet='sheet1$';
run;
proc sql;
select fname,
length(fname) as len,
index(fname, 'a') as ind1,
index(fname,'rao') as ind2,
scan(fname,3) as sc,
upcase(fname) as up,
compress(fname,'a') as comp1,
compress(fname) as comp2
 from demo1;
quit;
proc sql;
select fname,
compress(fname) as comp2,
substr(fname,3,10) as sub1,
substr(fname,3) as sub2
 from demo1;
 quit;
proc import datafile='C:\Documents and Settings\mobileclub\Desktop\source\xls\data.xls'
out=mh dbms=excel replace;
sheet='sheet3$';
run;
proc sql;
select pid,drug,dose,scan(adevents,1) as ae,
scan(adevents,2) as adr from mh;
quit;
proc import datafile='C:\Documents and Settings\mobileclub\Desktop\source\xls\data.xls'
out=mh1 dbms=excel replace;
sheet='sheet2$';
run;
proc sql;
select pid,drug ||'-'||dose as treatment from mh1;
quit;