*Slide 3;
/********************************************************************************/
/* Example of how %LET statements are processed before the DATA Step is executed*/
/********************************************************************************/

data books;
input rank 1. +1 title $21.;
if rank=1 then do; 
%let titletext=Top Bestseller; 
end;
else do; 
%let titletext=Other Bestsellers; 
end;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;

proc print;
where rank=1;
title "&titletext";
run;

*Slide 6;
/********************************************************************************/
/* Example of Using SYMPUT with a Literal                                       */
/********************************************************************************/

data books;
input rank 1. +1 title $21.;
call symput ('myfavorite','The Hobbit');
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;

proc print;
title "These are the Bestsellers, but '&myfavorite' is the best!";
run;

*Slide 8;
/********************************************************************************/
/* Example of Using SYMPUT with a DATA Step Variable                            */
/********************************************************************************/

data books;
input rank 1. +1 title $21.;
If rank=1 then do; 
call symput ('topseller',title); 
call symput ('rank',rank); end;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;
*Leading spaces will be addressed shortly;
options nopage nonumber; *Reduce clutter at header;
proc print;
title "Bestseller List with #&rank-Ranked '&topseller.'";
run;


*Slide 11;
/********************************************************************************/
/* Example of DATA step functions useful for removing blanks or characters      */
/********************************************************************************/

data books;
input rank 1. +1 title $21.;
If rank=1 then do;
call symput ('topseller',trim(title)); 
call symput ('rank',compress(rank)); 
end;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;
proc print;
title "Bestseller List with #&rank-Ranked '&topseller.'";
run;

*Slide 13;
/********************************************************************************/
/* Example of Using SYMPUTX with a DATA Step Variable                           */
/********************************************************************************/

data books;
input rank 1. +1 title $21.;
If rank=1 then do; 
call symputx ('topseller',title); 
call symputx ('rank',rank); 
end;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;
proc print;
title "Bestseller List with #&rank-Ranked '&topseller.'";
run;


*Slide 15;
/********************************************************************************/
/* Example of Using PUT with two numeric formats                                */
/********************************************************************************/

*Make sure ecoli is saved as a permanent SAS data set;

libname perm '/home/grego1/STAT 541';
%let month=JAN;
title "E Coli Data for &month 2009";
data perm.ecoli&month;
set perm.ecoli ;
cdate=put(collection_date,date9.);
cmonth=substr(cdate,3,3);
if cmonth="&month";
run;

data ecoli&month; set perm.ecoli&month end=last;
total+1;
if fecalcoli/ecoli lt 1 then etotal+1;
if last then do;
*TRIM removes trailing blanks, while LEFT moves leading blanks to end of string;
call symput ('highec',trim(left(put(etotal,f5.0))));
*This step works for html format too;
*call symput ('highec',put(etotal,f5.0));
call symput ('nct',trim(left(put(total,f5.0))));
end;
run;
title2 "E Coli exceeded Fecal Coli for &highec records out of &nct records";
proc print data=ecoli&month (obs=10);
run;

data books;
input rank 1. +1 title $21.;
If rank=1 then do; 
call symput ('topseller',trim(title)); 
call symput ('rank',put(rank,1.)); 
end;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;
proc print;
title "Bestseller List with #&rank-Ranked '&topseller.'";
run;

*Slide 18;
/********************************************************************************/
/* Example of Creating Multiple Macro Variables During DATA Step Execution       */
/********************************************************************************/

data books;
input rank 1. +1 title $21.;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;

data _null_;
set books;
call symput('rank'||put(rank,1.),title);
run;
%put _user_;

*Slide 20;
/********************************************************************************/
/* Example of Referencing Macro Variables Indirectly                            */
/********************************************************************************/

data books;
input Rank 1. +1 title $21.;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;

data _null_;
set books;
call symput('rank'||put(rank,1.),title);
run;

options symbolgen;
%let titleforrank=rank1;
proc print; var rank;
where rank=input(substr("&titleforrank",5,1),1.);
title "Rank for &&&titleforrank";
run;

*This won't print the title correctly;
%let titleforrank=rank1;
proc print noobs; var rank;
where rank=input(substr("&titleforrank",5,1),1.);
title "Rank for &&titleforrank";
run;

*Resolves to same mistake as above;
%let titleforrank=rank1;
proc print noobs; var rank;
where rank=input(substr("&titleforrank",5,1),1.);
title "Rank for &titleforrank";
run;

%let titleforrank=rank3;
proc print noobs; var rank;
where rank=input(substr("&titleforrank",5,1),1.);
title "Rank for &&&titleforrank";
run;


*Slide 23;
/**************************************************************************************************/
/* Example of Using the SYMGET Function to Obtain Macro Variable Values During DATA Step Execution*/
/**************************************************************************************************/

data books;
input Rank 1. +1 Title $21.;
cards;
1 A Tale of Two Cities
2 The Lord of the Rings
3 The Hobbit
;
run;

data _null_;
set books;
call symput('rank'||put(rank,1.),title);
run;

data books2;
*Add rank here;
set books (keep=rank);
length title $21.;
*Add title here;
title=symget('rank'||put(rank,1.));
proc print;
run;

*Slide 25;
/******************************************/
/* Example of Using INTO with PROC SQL    */
/******************************************/

title;
data bbstats;
label atbats="At Bats";
input Player $19. atbats Hits BB;
datalines;
Christian Walker     271 97 36
Scott Wingo          240 81 44
Brady Thomas         231 73 23
Evan Marzilli        220 64 25
Robert Beary         211 61 12
Adrian Morales       249 70 30
Peter Mooney         254 71 44
Jake Williams        209 56 21
Jackie Bradley Jr.   162 40 22
;
run;

proc sql;
  create table bastats as select player, hits/atbats as ba label="Batting Average" format=f4.3  from bbstats;
select * from bastats;
quit;

proc sql;
  select sum(hits)/sum(atbats) format=f4.3 label="Team BA" into :teamba trimmed
  from bbstats;
quit;
* Old fix no longer needed with TRIMMED command;
*%let teamba=&teamba; 

proc print data=bastats label noobs;
title "USC Batting Averages";
title2 "Team Batting Average is &teamba";
run;

*Slide 25;
/******************************************************************************************/
/* Example of Using INTO with PROC SQL to create macro variables for every data record    */
/******************************************************************************************/

Libname perm "/home/grego1/STAT 541";

data meddb;
set perm.meddb;
run;

proc sql;
create table tosclaim as select tos, count(*) as nct, sum(claim) as totalclaim
from meddb
group by tos;
select * from tosclaim;
quit;

proc sql;
*Special syntax to print special character (#);
select count(*) label="%bquote("# of Claim Types")" into :nrec trimmed from tosclaim;
*Old fix--no longer needed with use of TRIMMED keyword;
* %let nrec=&nrec;

select tos label="Type of Service", nct label="Count", totalclaim
 into :tos1-:tos&nrec,
      :nct1-:nct&nrec,
      :totalclaim1-:totalclaim&nrec
 from tosclaim;
%put _user_;

proc sql;
select distinct tos into :tostype separated by ', '
 from meddb;
%put Types of service: &tostype;