libname shrug "e:\Shrug";

*import macro;
%macro imp1(sht=,dout=,din=);
PROC IMPORT OUT=&dout
DATAFILE= "e:\Shrug\&din" 
DBMS=EXCEL REPLACE;
SHEET=&sht; 
GETNAMES=YES; 
MIXED=NO; 
SCANTEXT=YES; 
RANGE="A4:K37"; 
RUN;
%mend imp1;

*execute macro 19 years of data;
%imp1(sht="T3_FBW_1989",dout=shrug.t3fbw_89,din=shrug091125.xls);
%imp1(sht="T3_FBW_1990",dout=shrug.t3fbw_90,din=shrug091125.xls);
%imp1(sht="T3_FBW_1991",dout=shrug.t3fbw_91,din=shrug091125.xls);
%imp1(sht="T3_FBW_1992",dout=shrug.t3fbw_92,din=shrug091125.xls);
%imp1(sht="T3_FBW_1993",dout=shrug.t3fbw_93,din=shrug091125.xls);
%imp1(sht="T3_FBW_1994",dout=shrug.t3fbw_94,din=shrug091125.xls);
%imp1(sht="T3_FBW_1995",dout=shrug.t3fbw_95,din=shrug091125.xls);
%imp1(sht="T3_FBW_1996",dout=shrug.t3fbw_96,din=shrug091125.xls);
%imp1(sht="T3_FBW_1997",dout=shrug.t3fbw_97,din=shrug091125.xls);
%imp1(sht="T3_FBW_1998",dout=shrug.t3fbw_98,din=shrug091125.xls);
%imp1(sht="T3_FBW_1999",dout=shrug.t3fbw_99,din=shrug091125.xls);
%imp1(sht="T3_FBW_2000",dout=shrug.t3fbw_00,din=shrug091125.xls);
%imp1(sht="T3_FBW_2001",dout=shrug.t3fbw_01,din=shrug091125.xls);
%imp1(sht="T3_FBW_2002",dout=shrug.t3fbw_02,din=shrug091125.xls);
%imp1(sht="T3_FBW_2003",dout=shrug.t3fbw_03,din=shrug091125.xls);
%imp1(sht="T3_FBW_2004",dout=shrug.t3fbw_04,din=shrug091125.xls);
%imp1(sht="T3_FBW_2005",dout=shrug.t3fbw_05,din=shrug091125.xls);
%imp1(sht="T3_FBW_2006",dout=shrug.t3fbw_06,din=shrug091125.xls);
%imp1(sht="T3_FBW_2007",dout=shrug.t3fbw_07,din=shrug091125.xls);

data shrug.t3_fbw_89_07;
set shrug.t3fbw_89 shrug.t3fbw_90 shrug.t3fbw_91 shrug.t3fbw_92 shrug.t3fbw_93 shrug.t3fbw_94 shrug.t3fbw_95
	shrug.t3fbw_96 shrug.t3fbw_97 shrug.t3fbw_98 shrug.t3fbw_99 shrug.t3fbw_00 shrug.t3fbw_01 shrug.t3fbw_02
	shrug.t3fbw_03 shrug.t3fbw_04 shrug.t3fbw_05 shrug.t3fbw_06 shrug.t3fbw_07;
length prov $ 5.;
n=_n_;

/*
*year;
if 1<=n<=33 then year=1989;
if 34<=n<=66 then year=1990;
if 67<=n<=99 then year=1991;
if 100<=n<=132 then year=1992;
if 133<=n<=165 then year=1993;
if 166<=n<=198 then year=1994;
if 199<=n<=231 then year=1995;
if 232<=n<=264 then year=1996;
if 265<=n<=297 then year=1997;
if 298<=n<=330 then year=1998;
if 331<=n<=363 then year=1999;
if 364<=n<=396 then year=2000;
if 397<=n<=429 then year=2001;
if 430<=n<=462 then year=2002;
if 463<=n<=495 then year=2003;
if 496<=n<=528 then year=2004;
if 529<=n<=561 then year=2005;
if 562<=n<=594 then year=2006;
if 595<=n<=627 then year=2007;
*prov;

do i=1 to 19;
 n33=(i-1)*33;
 n_n33=n-n33;
if 1<=n_n33<=33 then go to skip1;
end;
skip1:
if 1<=n_n33<=3 then prov="NF";
if 4<=n_n33<=6 then prov="PEI";
if 7<=n_n33<=9 then prov="NS";
if 10<=n_n33<=12 then prov="NB";
if 13<=n_n33<=15 then prov="QC";
if 16<=n_n33<=18 then prov="ONT";
if 19<=n_n33<=21 then prov="MTB";
if 22<=n_n33<=24 then prov="SASK";
if 25<=n_n33<=27 then prov="ALB";
if 28<=n_n33<=30 then prov="BC";
if 31<=n_n33<=33 then prov="CND";
return;
*/

n_n33=mod(n,33);
if n_n33=0 then n_n33=33;

if 1<=n_n33<=3 then prov="NF";
if 4<=n_n33<=6 then prov="PEI";
if 7<=n_n33<=9 then prov="NS";
if 10<=n_n33<=12 then prov="NB";
if 13<=n_n33<=15 then prov="QC";
if 16<=n_n33<=18 then prov="ONT";
if 19<=n_n33<=21 then prov="MTB";
if 22<=n_n33<=24 then prov="SASK";
if 25<=n_n33<=27 then prov="ALB";
if 28<=n_n33<=30 then prov="BC";
if 31<=n_n33<=33 then prov="CND";

retain yi 0;
if n_n33=1 then yi=yi+1;else yi=yi;year=1988+yi;

n3=mod(n,3);
run;

proc sort data=shrug.t3_fbw_89_07;
by prov year;
run;
title;

proc gplot data=shrug.t3_fbw_89_07;
by prov;
plot f6*year;
where n3=0;
title "Shrug f6 by prov";
run;

proc gplot data=shrug.t3_fbw_89_07;
by prov;
plot f11*year;
where n3=2;
title "Shrug f11 by prov";
run;