* Datamart.sas;

* "A data mart is simply a mini-data warehouse."
   http://www.pharmacyinformatics.org/datawarehouse.htm

   Epidemiologist - Define the questions.
   Programmer     - Organize the data and write programs that can be easily run  
                    to answer the most commonly-asked questions;

libname data "c:\SyndromicWorkshop\sas\data";

* Create macro indicating where the data for this workshop are kept; 
%LET FOLDER=c:\SyndromicWorkshop\sas\;

%include "&FOLDER\codesyndromes.sas";

****look at what type of variables you have;
proc contents data=codedarchive; run;

**create summary table by edcode, date, and syndrome;
proc sql;
create table syndrome1 as
select date, edcode, syndrome, count(*) as syncount
from codedarchive
group by date, edcode, syndrome;

***change syndrome(1,2,3) into columns of data;
proc sql;
create table syndrome2 as
select date, edcode, sum(syndrome=5) as cold, 
sum(syndrome=2) as resp
count(*) as total
from codedarchive
group by date, edcode;

**create summary table by edcode, date, and diarrhea syndrome from
diar column;
proc sql;
create table syndrome3 as
select date, edcode, 
sum (diar=1) as diarrhea
from codedarchive
group by date, edcode;

***two ways to roll-up data;
***BY EDCode, DATE-for spatial analysis;
proc sql;
create table syndrome4 as
select date, edcode, sum(syndrome=5) as cold, 
sum(syndrome=2) as resp
count(*) as total
from codedarchive
group by date, edcode;

****BY DATE-for citywide time series analysis;
proc sql;
create table syndrome5 as
select date, 
sum (diar=1) as diarrhea
from codedarchive
group by date;