%let name=fema_disaster_declarations_import; filename odsout '.'; /* Creating a map similar to: http://oldsite.osogrande.com/images/disastermap1.png Using data from: http://www.fema.gov/disasters/grid/state */ %include '../data_vault/proxy.sas'; /* I %include a file that sets the my_proxy macro variable. You won't be able to use my proxy, therefore set your own macro variable, which will be something like the following: %let my_proxy=http://yourproxy.com:80; */ %let dataname=femadeclarations; libname robsdata '.'; /* This bit of tricky code downloads the xls spreadsheet on-the-fly, so you don't have to do it manually :) */ filename xlsfile url "http://www.fema.gov/library/file;jsessionid=2155FE9D4AC9CB597C34A11E10ABA62A.Worker2Public2?type=publishedFile&file=data.gov.femadeclarations.xls&fileid=afb3fb20-9570-11e2-8e7d-001cc456982e" proxy="&my_proxy"; data _null_; n=-1; infile xlsfile recfm=s nbyte=n length=len _infile_=tmp; input; file "&dataname..xls" recfm=n; put tmp $varying32767. len; run; PROC IMPORT OUT=raw_data DATAFILE="&dataname..xls" DBMS=XLS REPLACE; RANGE='FEMA Declarations 1 of 1$A3:N50000'; GETNAMES=YES; MIXED=NO; RUN; data raw_data; set raw_data (where=(disaster_number^=. and state not in ('PR' 'VI' 'FM' 'GU' 'MH' 'AS' 'MP' 'PW')) ); run; data county_data; set raw_data (where=(Declared_County_Area^='Statewide')); Declared_County_Area=trim(left(scan(Declared_County_Area,1,'('))); if (state='MO' and Declared_County_Area='Jefferson City') then Declared_County_Area='Jefferson'; if (state='IL' and Declared_County_Area='La Salle') then Declared_County_Area='LaSalle'; if (state='NM' and Declared_County_Area='DeBaca') then Declared_County_Area='De Baca'; if (state='FL' and Declared_County_Area='Dade') then Declared_County_Area='Miami-Dade'; run; proc sql; create table county_data as select unique county_data.*, us_all_attr.county from county_data left join mapsgfk.us_all_attr on (county_data.state=us_all_attr.statecode) and (county_data.Declared_County_Area=us_all_attr.idname); quit; run; /* Looks like they list out all the counties in the xls anyway, so this part is not necessary */ /* data whole_states (drop=Declared_County_Area); set raw_data (where=(Declared_County_Area='Statewide')); run; proc sql; create table whole_states as select unique whole_states.*, us_all_attr.idname as Declared_County_Area, us_all_attr.county from whole_states left join mapsgfk.us_all_attr on (whole_states.state=us_all_attr.statecode); quit; run; */ data all_data; set county_data /* whole_states */; run; proc sort data=all_data out=all_data; by disaster_number state county; run; data robsdata.&dataname; set all_data; run; goptions device=png; ODS LISTING CLOSE; ODS HTML path=odsout body="&name..htm" (title="FEMA") style=htmlblue; goptions gunit=pct htitle=5.0 ftitle="albany amt/bold" htext=2.7 ftext="albany amt/bold" ctext=white; proc sql; create table foo as select unique state, Declared_County_Area from robsdata.&dataname where county=. and Declared_County_Area^='' order by state, Declared_County_Area; quit; run; title1 "Here are the ones I couldn't find a matching fips county code for in mapsgfk.us_all_attr"; proc print data=foo; var state Declared_County_Area; run; quit; ODS HTML CLOSE; ODS LISTING;