%macro pl94trnd(inset=,inset90=,sumlevs=,state=,reportby=state,reportby2=,byvar=,lgeo=32, byvars=sumlev geocode,csvfiles=1,maxobs=99999,skeleton=0,arange=,title2=,sort90=0); %*--Macro to generate a standard census 2000 PL94-based trend report. Of course, it will only be a true trend report if inset90 is specfied indicating that you have created comparable data from the 1990 census for the same geography and demographic categories (where it makes sense - some of the categories were not defined in 1990.) Rev. 5.15.01 to add reportby2 and the lgeo (length of geoarea var) parm. John Blodgett, Missouri Census Data Center / OSEDA/ March, 2001. --*; %if &skeleton %then %do; %*--create skeleton data set for variable attribs in the pl942000 data lib-; *------Create a skeleton of the report file with variable attributes---------------*; data pl942000.trendr_skeleton(label='Skeleton data set for use with Basic Trend report'); *--define the variables in their left-to-right order in the report--; attrib GeoArea length=$32 label='Geographic Area'; *--Name of area with geocode in parens, e.g. Adair County (29001)-; attrib Year length=$4 ; *--Should have a value of '1990' or '2000'--; attrib PopGroup length=3 label='Population Group' format=popgrp.; *--we store the population group as a numeric code to be turned into a readable label with a custom format--; attrib Values length=$21 label='Value(s) - All Ages' format=$char21.; *--we need to format the values within this character string variable. For race items we can generate an interval such as "bbbbb12,345-12,567" indicating the lower and upper bounds of persons in the race category. For many PopGroups and for all 1990 data there will just be a single, centered value in this field.; attrib Pcts length=$12 label='Pcts' format=$char12.; *--will contain the percentages corresponding to the values stored in Values. Also may be an interval in format xx.x-xxx.x or a single centered pct in 5.1 format.; attrib Values18 length=$21 label='Over 18' format=$char21.; *--See values: same idea but qualified to use only counts of persons age 18 or over--; attrib Pcts18 length=$12 label='Pcts, 18+' format=$char12.; stop; run; %end; *%---if skeleton code-; %let arange=%quote(&arange); %*--in case they code "a-d"-; %let title3=; %if %length(&arange)=2 or %length(&arange)=3 %then %do; %*--a value of ad will cause pgm to filter input obs so that only those with areanames beginning with A thru D will be kept.----; %local alow ahigh; %let alow=%substr(&arange,1,1); %if %length(&arange)=2 %then %let ahigh=%substr(&arange,2,1); %else %let ahigh=%substr(&arange,3,1); %let alow=%upcase(&alow); %let ahigh=%upcase(&ahigh); %let afilter=%str(where also ) "&alow" <=: areaname <=: "&ahigh" %str(;) ; %let title3=%str(Only Places Starting With Letters &alow thru &ahigh Included in This Report); %end; %else %let afilter=%str(); %if %nrbquote(&title2) ne %str() %then %do; title2 "&title2"; %end; %if %nrbquote(&title3) ne %str() %then %do; title3 "&title3"; %end; %if &sort90 %then %do; data inset90; set &inset90; where sumlev in (%qlist(&sumlevs)) and totpop > 0; &afilter run; proc sort data=inset90; by &byvars; %let inset90=inset90; %end; data pl94trnd; length GeoArea $&lgeo ; *--override skeleton--; if 0 then set pl942000.trendr_skeleton; *--sets up the pdv and assigns labels, etc.-; set &inset %if %bquote(&inset90) ne %str() %then %do; &inset90 (in=in90) %end; ; where sumlev in (%qlist(&sumlevs)) and totpop > 0; &afilter by &byvars; if _n_ gt &maxobs then stop; *<---use this parm for test runs--; if sumlev='140' and areaname=:'Census ' then areaname=substr(areaname,8); *--Edit "Census Tract .." to "Tract "; %if %bquote(&inset90) eq %str() %then %do; retain in90 0; drop in90; %end; if state=' ' then state="&state"; *<---added 3-19-01--; _lgeoarea= length(areaname) + length(geocode) +3; drop _lgeoarea; if _lgeoarea gt &lgeo then do; *--try to edit names that are too long--; IF INDEX(AREANAME,'SCHOOL DISTRICT') THEN AREANAME=TRANWRD(AREANAME,'SCHOOL DISTRICT','SCH.DST.'); else IF INDEX(AREANAME,'SCHOOL ') THEN AREANAME=TRANWRD(AREANAME,'SCHOOL ','SCH '); else if index(areaname,'County') then areaname=tranwrd(areaname,'County','Co.'); end; Geoarea=trim(areaname)||' ('||trim(compress(geocode,'-'))||')'; *--e.g. "California (06)".--; if not in90 then year='2000'; else year='1990'; if year='1990' then do; array uplims(*) white2 black2 asian2 indian2 hawnpi2 other2 whitenh2 whovr182 blovr182 asovr182 inovr182 haovr182 otovr182 wnovr182 ; array lolims(*) white1 black1 asian1 indian1 hawnpi1 other1 whitenh1 whovr181 blovr181 asovr181 inovr181 haovr181 otovr181 wnovr181 ; do _i_=1 to dim(uplims); uplims(_i_)=lolims(_i_); *--by setting this phantom upper limit value to the same as the lower limit we cause the program logic to print only the single value, which is all that make sense for 1990.--; end; end; *--if year='1990' processing--; _pfact=100/totpop; if over18 then _pfact18=100/over18; else _pfact18=.; *--total pop--; popgroup=1; values=put(totpop,comma12.); pcts='100.0'; values18=put(over18,comma12.); *_pct=over18*_pfact; * pcts18=put(_pct,5.1); *--changed our mind. Going with pct of total over 18 instead of the over18 in this row as pct of the total pop for the row (popgroup); pcts18='100.0'; link output; *--hispanic--; popgroup=2; values=put(hisppop,comma12.); _pct=hisppop*_pfact; pcts=put(_pct,5.1); values18=put(hisovr18,comma12.); _pct=_pfact18*hisovr18; pcts18=put(_pct,5.1); link output; *--White--; popgroup=3; if white1=white2 then do; values=put(white1,comma12.); _pct=white1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(white1,comma9.)||' -'|| put(white2,comma9.); _pct1=white1*_pfact; _pct2=white2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if whOvr181=whOvr182 then do; values18=put(whOvr181,comma9.); _pct=_pfact18*whovr181; pcts18=put(_pct,5.1); end; else do; values18=put(whovr181,comma9.)||' -'|| put(whovr182,comma9.); _pct1=_pfact18*whovr181; _pct2=_pfact18*whovr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; *--White, non-hispanic--; popgroup=4; if whitenh1=whitenh2 then do; values=put(whitenh1,comma12.); _pct=whitenh1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(whitenh1,comma9.)||' -'|| put(whitenh2,comma9.); _pct1=whitenh1*_pfact; _pct2=whitenh2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if WNOvr181=WNOvr182 then do; values18=put(WNOvr181,comma9.); _pct=_pfact18*WNOvr181; pcts18=put(_pct,5.1); end; else do; values18=put(WNOvr181,comma9.)||' -'|| put(WNOvr182,comma9.); _pct1=_pfact18*WNOvr181; _pct2=_pfact18*WNOvr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; *--Black or African American--; popgroup=5; if black1=black2 then do; values=put(black1,comma12.); _pct=black1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(black1,comma9.)||' -'|| put(black2,comma9.); _pct1=black1*_pfact; _pct2=black2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if BlOvr181=BlOvr182 then do; values18=put(BlOvr181,comma9.); _pct=_pfact18*BlOvr181; pcts18=put(_pct,5.1); end; else do; values18=put(BlOvr181,comma9.)||' -'|| put(BlOvr182,comma9.); _pct1=_pfact18*BlOvr181; _pct2=_pfact18*BlOvr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; if year='2000' then do; *--Asian--; popgroup=6; if asian1=asian2 then do; values=put(asian1,comma12.); _pct=asian1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(asian1,comma9.)||' -'|| put(asian2,comma9.); _pct1=asian1*_pfact; _pct2=asian2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if AsOvr181=AsOvr182 then do; values18=put(AsOvr181,comma9.); _pct=_pfact18*AsOvr181; pcts18=put(_pct,5.1); end; else do; values18=put(AsOvr181,comma9.)||' -'|| put(AsOvr182,comma9.); _pct1=_pfact18*AsOvr181; _pct2=_pfact18*AsOvr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; end; else do; *--year is 1990---; aspovr18=max(aspovr18,aspovr181); *--lets us use either name for the variable-; *--Asian or Pacific Islander--*; popgroup=6.5; values=put(asianpi,comma12.); _pct=asianpi*_pfact; pcts=put(_pct,5.1); values18=put(aspovr18,comma12.); _pct=aspovr18*_pfact18; pcts18=put(_pct,5.1); link output; end; *--American Indian, Alaska native--; popgroup=7; if indian1=indian2 then do; values=put(indian1,comma12.); _pct=indian1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(indian1,comma9.)||' -'|| put(indian2,comma9.); _pct1=indian1*_pfact; _pct2=indian2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if InOvr181=InOvr182 then do; values18=put(InOvr181,comma9.); _pct=_pfact18*InOvr181; pcts18=put(_pct,5.1); end; else do; values18=put(InOvr181,comma9.)||' -'|| put(InOvr182,comma9.); _pct1=_pfact18*InOvr181; _pct2=_pfact18*InOvr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; if year='2000' then do; *--Hawaiian or other PI--; popgroup=8; if hawnPI1=hawnPI2 then do; values=put(hawnPI1,comma12.); _pct=hawnPI1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(hawnPI1,comma9.)||' -'|| put(hawnPI2,comma9.); _pct1=hawnPI1*_pfact; _pct2=hawnPI2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if HaOvr181=HaOvr182 then do; values18=put(HaOvr181,comma9.); _pct=_pfact18*HaOvr181; pcts18=put(_pct,5.1); end; else do; values18=put(HaOvr181,comma9.)||' -'|| put(HaOvr182,comma9.); _pct1=_pfact18*HaOvr181; _pct2=_pfact18*HaOvr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; end; *--Some other race--; popgroup=9; if Other1=Other2 then do; values=put(Other1,comma12.); _pct=Other1*_pfact; pcts=put(_pct,5.1); end; else do; values=put(Other1,comma9.)||' -'|| put(Other2,comma9.); _pct1=Other1*_pfact; _pct2=Other2*_pfact; pcts=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; if OtOvr181=OtOvr182 then do; values18=put(OtOvr181,comma9.); _pct=_pfact18*OtOvr181; pcts18=put(_pct,5.1); end; else do; values18=put(OtOvr181,comma9.)||' -'|| put(OtOvr182,comma9.); _pct1=_pfact18*OtOvr181; _pct2=_pfact18*OtOvr182; pcts18=put(_pct1,4.1)||' -'||put(_pct2,5.1); end; link output; *--total multiracial--; popgroup=10; if year='2000' then do; values=put(multrace,comma12.); _pct=multrace*_pfact; pcts=put(_pct,5.1); values18=put(mrovr18,comma12.); _pct=_pfact18*mrovr18; pcts18=put(_pct,5.1); link output; end; return; output: *--for the value strings just remove all the blanks and replace the dash with ' - ' for spacing. proc report will now center these strings--; values=compress(values); values=tranwrd(values,'-',' - '); values18=compress(values18); values18=tranwrd(values18,'-',' - '); pcts=trim(left(pcts))||'%'; pcts=compress(pcts); pcts=tranwrd(pcts,'-',' - '); pcts18=trim(left(pcts18))||'%'; pcts18=compress(pcts18); pcts18=tranwrd(pcts18,'-',' - '); output; return; keep state &byvar &reportby &reportby2 geo_id geoarea--pcts18; run; proc report data=pl94trnd nowd headline headskip spacing=1 missing ; *by state notsorted; * format state $state.; *label state='State'; %if &byvar ne %str() %then %do; by &byvar; %end; %if &reportby ne %str() %then %do; column &reportby; define &reportby /order order=data noprint; compute before _page_; if _break_ ne "&reportby" then line @1 "&reportby: " &reportby $&reportby..; %if &reportby2 ne %str() %then %do; if _break_ ne "&reportby2" then line @1 "&reportby2: " &reportby2 $&reportby..; %end; endcomp; compute before &reportby; line @1 "&reportby: " &reportby $&reportby..; endcomp; break after &reportby / skip; %end; %if &reportby2 ne %str() %then %do; column &reportby2; define &reportby2 /order order=data noprint; *compute before _page_; compute before &reportby2; line @1 "&reportby2: " &reportby2 $&reportby2..; endcomp; break after &reportby2 / skip; %end; column geoarea year popgroup; column ('-Total-' values pcts); column ('-Over 18-' values18 pcts18); *define state / noprint order order=data; define geocode /order order=data noprint; define geoarea /order order=data 'Geographic Area'; define year /order order=internal descending; define popgroup / LEFT format=popgrp. 'Population Group'; define values18 / CENTER 'Value(s)'; define pcts18 / CENTER 'Percent'; define values / CENTER 'Value(s)'; define pcts / CENTER 'Percent'; /*<=========================Comment out===================================== compute before state; line @1 'State: ' state $state.; endcomp; break after state/dol skip; ===========================End commented out================================ */ *break after geoarea / dul; break after year/ skip; run; proc printto ; run; *--redirect printed output to avoid variables list generated by cnvtdlm--; %if &csvfiles %then %do; %if &pdf %then %str( ods printer close; ); %if %quote(&inset) ne %str() %then %do; %*--generate csv file with 2000 data--; filename dlmout "&htmldir/&htmlfile.2000.csv"; data csvdata/view=csvdata; set &inset; where sumlev in (%qlist(&sumlevs)) and totpop > 0; keep sumlev &reportby geocode areaname state _numeric_; %if %nrbquote(&reportby)=county or %nrbquote(&reportby)=County %then %str( format county $county.;); run; %cnvtdlm(setin=csvdata,names=1,round=.1) run; filename dlmout clear; %end; %if %quote(&inset90) ne %str() %then %do; %*--generate csv file with 1990 data--; filename dlmout "&htmldir/&htmlfile.1990.csv"; data csvdata2/view=csvdata2; set &inset90; keep sumlev &reportby geocode areaname state _numeric_; %if %nrbquote(&reportby)=county or %nrbquote(&reportby)=County %then %str( format county $county.;); where sumlev in (%qlist(&sumlevs)) and totpop > 0; run; %cnvtdlm(setin=csvdata2,names=1,round=.1) run; %end; %end; %*--if csvfiles code--; %mend pl94trnd;