/**********************************************************************/ data sample; input code $1. date date7.; cards; A 01JAN93 /* Fri */ a 06JAN93 /* Wed */ a 11JAN93 /* Mon */ a 18JAN93 /* Mon */ A 22JAN93 /* Fri */ A 26JAN93 /* Tue */ a 26JAN93 /* Tue */ A 04FEB93 /* Thu */ A 08FEB93 /* Thu */ A 08FEB93 /* Mon */ a 12FEB93 /* Fri */ a 19FEB93 /* Fri */ A 22FEB93 /* Mon */ ; /* OUTPUT 1a and 1b */ proc report data=sample nowindows headskip; column code date n; define code / group format=$4.; define date / group format=weekdate3. order=internal "DAY"; run; /**********************************************************************/ data temp; set sample; retain fmtname 'dayfmt' type 'N'; day=weekday(date); label=put(date,weekdate3.); run; /**********************************************************************/ proc sort data=temp(keep=fmtname day label type) out=control nodupkey; by day; run; /**********************************************************************/ proc format cntlin=control(rename=(day=start)); run; /**********************************************************************/ proc sort data=temp(keep=code day) out=dummy ebcdic nodupkey; by code day; run; /**********************************************************************/ data sample2; set dummy sample(in=is_valid); if is_valid then do; valid=1; day=weekday(date); end; drop date; run; /**********************************************************************/ /* OUTPUT 2 */ proc report data=sample2 nowindows headskip; column code day valid; define code / group order=data format=$4.; define day / group order=internal format=dayfmt4.; define valid / n format=1. "N"; run; /**********************************************************************/ /* OUTPUT 3 */ proc tabulate data=sasuser.houses noseps format=3. order=freq; class bedrooms style; table bedrooms*(style=' ' all), n / rts=20; run; /**********************************************************************/ /* OUTPUT 4 */ proc tabulate data=sasuser.houses noseps format=3. order=freq; class style; table style=' ', n / rts=10; run; /**********************************************************************/ proc format; /* formats corresponding to order 1 */ value ordr1fmt 1='2' 2='4' 3='3' 4='1'; /* formats corresponding to order 2 */ value ordr2fmt 1,6,9 ='CONDO' 2,4 ='TWOSTORY' 3,7,10='RANCH' 5,8,11='SPLIT'; data dbl_map; input order1 order2 order3 count; cards; 1 1 1 2 1 2 2 2 1 3 3 1 2 4 1 2 2 5 2 1 2 6 3 1 3 7 1 2 3 8 2 1 3 9 3 1 4 10 1 1 4 11 2 1 ; run; /**********************************************************************/ /* OUTPUT 5 */ proc tabulate data=dbl_map noseps format=3.; options label; class order1 order2 order3; freq count; table order1*(order3*order2=' ' all), n / rts=29; label order1='BEDROOMS'; format order1 ordr1fmt. order2 ordr2fmt.; run; /**********************************************************************/ /* OUTPUT 6 */ proc report data=dbl_map nowindows headline; column order1 order3 order2 n; freq count; define order1 / group order=internal format=ordr1fmt8. "BEDROOMS"; define order2 / group order=internal format=ordr2fmt. "STYLE"; define order3 / group noprint; break after order1 / ol summarize skip; run; /**********************************************************************/ /* step 1a: summarize and compute ORDRSTAT */ proc summary data=sasuser.houses; class bedrooms style; var price; output out=summary mean=ordrstat; run; /**********************************************************************/ /* step 1b: summarize and create ORDRSTAT by renaming */ /* _FREQ_ */ proc summary data=sasuser.houses; class bedrooms style; output out=summary(rename=(_freq_=ordrstat)); run; /**********************************************************************/ /* step 2: sort by ORDRSTAT */ proc sort data=summary; by descending ordrstat; run; /**********************************************************************/ /* step 3: create CONTROL1 and CONTROL1 */ data control1(drop=order2 _type_ style) control2(drop=order1 _type_); set summary; retain type 'N'; if _type_=2 then do; order1+1; fmtname='ordr1fmt'; output control1; end; else if _type_=3 then do; order2+1; fmtname='ordr2fmt'; output control2; end; run; /**********************************************************************/ /* step 4: create the formats */ proc format cntlin=control1(rename=(order1=start bedrooms=label)); proc format cntlin=control2(rename=(order2=start style =label)); value blank .,0 =' ' other=(|dollar8.|); picture rank 1 ='1st' 2 ='2nd' 3 ='3rd' 4-20='09th'; run; /**********************************************************************/ /* step 5: create the WORK.SUMMARY2 */ proc sort data=control1; by bedrooms; proc sort data=control2; by bedrooms; data summary2; merge control1 control2; by bedrooms; drop fmtname type; run; /**********************************************************************/ /* step 6a: create WORK.DBL_MAP1 to be used either */ /* directly with a procedure or merged with a */ /* small primary data set in step 7a */ proc sort data=summary2; by order1 order2; data dbl_map1; set summary2; by order1 order2; if first.order1 then order3=0; order3+1; run; /**********************************************************************/ /* step 6b: create WORK.DBL_MAP1 to be merged with a */ /* large primary data in step 6b */ proc sort data=summary2; by order1 order2; data dbl_map1(index=(o3_index=(bedrooms style))); set summary2; by order1 order2; if first.order1 then order3=0; order3+1; run; /**********************************************************************/ /* step 7a: create a double mapped data set that has */ /* not been summarized using WORK.DBL_MAP1 */ /* from step 6a as input */ proc sort data=dbl_map1(drop=_freq_); by bedrooms style; proc sort data=sasuser.houses out=houses; by bedrooms style; data dbl_map2; merge dbl_map1 houses; by bedrooms style; run; /**********************************************************************/ /* step 7b: create a double mapped data set that has */ /* not been summarized using WORK.DBL_MAP1 */ /* from step 6b as input */ data dbl_map2; set sasuser.houses; set dbl_map1 key=o3_index; run; /**********************************************************************/ /* OUTPUT 7 */ proc report data=dbl_map2 nowindows headskip; column order1 order3 order2 price sqfeet baths; define order1 / "BEDROOMS" group format=ordr1fmt8. order=internal; define order3 / noprint group order=internal; define order2 / "STYLE" group format=ordr2fmt8. order=internal left; define price / "PRICE" mean format=blank.; define sqfeet / "SQFEET" mean format=6.; define baths / "BATHS" mean format=6.1; break after order1 / ol summarize skip; run; /**********************************************************************/ /* OUTPUT 8 */ proc report data=dbl_map2 nowindows spacing=1; column order3 order1,("--" order2 ordrstat); define order3 / group order=internal noprint; define order1 / across order=internal format=ordr1fmt. "--/BEDROOMS/--"; define order2 / mean left format=ordr2fmt. "STYLE/--"; define ordrstat / mean format=blank. "PRICE/--"; rbreak after / dol summarize; compute after; _c2_ = .; _c4_ = .; _c6_ = .; _c8_ = .; endcomp; run; /**********************************************************************/ /* OUTPUT 9 */ proc report data=dbl_map1 nowindows spacing=2; where (order3<=3); freq _freq_; column ("--/BEDROOMS/--" order1 ordrstat=tot) order3,("--" order2 ordrstat); define order1 / group format=ordr1fmt. order=internal "#/-"; define tot / mean format=blank9. "AVG PRICE/--"; define order3 / across format=rank. order=internal "TOP 3 STYLES/--"; define order2 / mean format=ordr2fmt. left "STYLE/--"; define ordrstat / mean format=blank. spacing=1 "PRICE/--"; run; /**********************************************************************/ /* OUTPUT 10 */ proc tabulate data=dbl_map1 format=blank. noseps; options label; where (order3<=3); freq _freq_; class order1 order3; var order2 ordrstat; table order1=' ', ordrstat*mean order3*(order2*mean*f=ordr2fmt. ordrstat*mean) / rts=7 box='COUNT OF +---+ BED ROOMS'; format order1 ordr1fmt. order3 rank.; label order2 ='STYLE' order3 ='TOP 3 STYLES' ordrstat='PRICE'; keylabel mean =' '; run; /**********************************************************************/ /* alternative approach to steps 5 and 6 */ proc sql; create view summary2 as select c1.order1, c1.bedrooms, c2.order2, c2.style, c2.ordrstat from control1 c1, control2 c2 where c1.bedrooms=c2.bedrooms order by c1.order1, c2.order2; quit; data dbl_map1; set summary2; by order1 order2; if first.order1 then order3=0; order3+1; run; /**********************************************************************/