*Let's use macro variables to sort claims by TOS
categories in descending size;

* Code to import data set that I have saved in a folder ;
* I generated this using the Import Data menu choice;

/* Generated Code (IMPORT) */
/* Source File: MedDB.csv */
/* Source Path: /home/davidhitchcock/sasuser.v94/stat541 */
/* Code generated on: Thursday, January 28, 2016 10:25:22 PM */

%web_drop_table(WORK.MEDDB);


FILENAME REFFILE "/home/davidhitchcock/sasuser.v94/stat541/MedDB.csv" TERMSTR=CR;

PROC IMPORT DATAFILE=REFFILE
	DBMS=CSV
	OUT=WORK.MEDDB;
	GETNAMES=YES;
	DATAROW=2;
RUN;

PROC CONTENTS DATA=WORK.MEDDB; RUN;


%web_open_table(WORK.MEDDB);

*Be sure to assign formats to meddb when you import it;
proc sql;
create table meddb as select ssn format=ssn11., btrim(tos) as tos label='Type of Service', dos format=mmddyy9. label='Date of Service', provider, claim format=dollar11.2 
from meddb;
quit;


*Check counts for TOS categories;
proc freq data=meddb;
table tos;
run;

*Logic for macro variables;
*TOS "A and B" creates a difficult problem otherwise;
proc sql;
create table tossort as select tos, count(*) as 
nct, compress(tos) as tosb
from meddb
group by tos
order by nct descending;

proc sql;
select count(*) into :nrec from tossort;

%let nrec=&nrec;
select tos label="Type of Service", tosb
into :tos1-:tos&nrec, :tosb1-:tosb&nrec from tossort;
quit;

%put _user_;

options symbolgen mprint mlogic;

data aa&tosb1 bb&tosb2 cc&tosb3 dd&tosb4;
set meddb;
if tos="&tos1" then output aa&tosb1;
else if tos="&tos2" then output bb&tosb2;
else if tos="&tos3" then output cc&tosb3;
else output dd&tosb4;
run;


*IF-THEN example;

DATA ALWEST ALEAST NLWEST NLEAST;
SET sashelp.baseball;
if league='American' and division='West' then output ALWEST;
else if league='American' and division='East' then output ALEAST;
else if league='National' and division='West' then output NLWEST;
else output NLEAST;
run;
proc sql; select * from ALWEST;
run;

*SELECT clause example;
data a;
input x @@;
select;
when(x<2) y=-x;
when(x<3);
when (x<4) y=10*x;
otherwise y=100*x;
end;
datalines;
1.2 2.5 3.9 4.6
;
run;

proc sql; select * from a;
run;

* When to use the WHERE statement:

* Data STEP, then PROC SORT;

data trucks;
set sashelp.cars;
where type in('SUV','Truck');
run;

proc sort data=trucks;
by type;
run;

proc print data=trucks;run;

* PROC SORT with a WHERE statement (maybe more efficient);

data trucks;
set sashelp.cars;
where type in('SUV','Truck');
run;

proc sort data=sashelp.cars out=trucks;  /* need to create separate (sorted) output data set */
by type;
where type in('SUV','Truck');
run;

proc print data=trucks;run;


* Example with OBS and FIRSTOBS;

PROC PRINT DATA = sashelp.baseball (obs=25);
run;


PROC PRINT DATA = sashelp.baseball (firstobs=2 obs=4);
WHERE league='American' and division='West';
run;

* Example with Subsetting IF;

DATA CTL2015C;
FILENAME webpage2 URL 'http://people.stat.sc.edu/hitchcock/tennissingleslists.txt';
INFILE webpage2 DLM='09'X firstobs=50 obs=102;
INPUT Name :$24. Gender $ City :$10. State $ Rating $ RatingDate MMDDYY10. RatingType $;
IF City='Columbia';
run;

proc print;run;

* More efficient to put subsetting IF earlier;

DATA CTL2015C;
FILENAME webpage2 URL 'http://people.stat.sc.edu/hitchcock/tennissingleslists.txt';
INFILE webpage2 DLM='09'X firstobs=50 obs=102;
INPUT Name :$24. Gender $ City :$10. @;  /* The @ symbol holds the observation in the buffer */
IF City='Columbia';
INPUT State $ Rating $ RatingDate MMDDYY10. RatingType $;
run;

proc print;run;

/* When the variables' position can be accessed by going directly to their places in the data lines, */
/* you can be very efficient */

data nonprofR;
input @36 since @;
IF since GE 2008;
input @1 memberid @5 Name $30. @41 years @44 Donations;
format donations dollar5.0;
datalines;
102 James and Roberta Edwards      2008 2  20
 23 Michaels Family                2003 7  30
  8 Charles Dobbs                  2003 7  20
 46 Eliza Singleton                2004 6  75
113 Mullen Jeffords                2010 0  20
;
run;

proc print data=nonprofR;
VAR memberid Name since years Donations;
title "Members who Joined 2008 or Later";
run;
title;


* Example of KEEP;

data cars2;
set sashelp.cars;
if origin='USA' then wt_listed = weight/2000;
else wt_listed = weight*0.4536;
run;
proc means data=cars2 mean median maxdec=2;
title 'Listed Weights';
class make;
var wt_listed;
run;

data cars2 (keep=make wt_listed);
set sashelp.cars;
if origin='USA' then wt_listed = weight/2000;
else wt_listed = weight*0.4536;
run;
proc means data=cars2 mean median maxdec=2;
title 'Listed Weights';
class make;
var wt_listed;
run;

data cars2 (keep=make wt_listed);
set sashelp.cars (keep=make weight origin);
if origin='USA' then wt_listed = weight/2000;
else wt_listed = weight*0.4536;
run;
proc means data=cars2 mean median maxdec=2;
title 'Listed Weights';
class make;
var wt_listed;
run;

data cars2;
set sashelp.cars (keep=make weight origin);
if origin='USA' then wt_listed = weight/2000;
else wt_listed = weight*0.4536;
run;
proc means data=cars2 (keep=make wt_listed) mean median maxdec=2;
title 'Listed Weights';
class make;
var wt_listed;
run;