proc sql; create table chart as select * from ( select monotonic() as id, var2, count ( *) as count from ( select distinct var1, var2 from report ) group by var2 ) where id < 5 ; quit; ... View more

It's easy to match the ID-TYPE in DataFile1 to ID-TYPE in DataFile2. But, I can see that you have a challenge on your hands trying to then figure out which IDs in DataFile1 are related to the same address so that you can output the ID associated with TYPE = A. There is no obvious key between TYPE = A and TYPE = B. It looks like you will have to use some kind of fuzzy merge matching to figure out which addresses in DataFile1 are related . . . You could try something like this (adapted from http://www.nesug.org/proceedings/nesug07/ap/ap23.pdf). Note: I'm not sure how well COMPGED deals with digits or punctuation. Also, you might want to strip out most punctuation and control characters before doing the comparison. %let maxscore=999; proc sql;   create table matches as   select a.id as a_id , a.address as addressA, b.id as b_id , b.address as addressB,           compged(a.address,b.address,&maxscore,'iL' ) as gedscore     from DataFile1 a, DataFile1 b    where a.id < b.id        and calculated gedscore < &maxscore    order by calculated gedscore; quit; ... View more

Astounding's approach is simple and effective. You could also do something like: proc sql; select distinct city_a into :refer separated by ' ' from reference; quit ; data want ; set have ; refer_list = "&refer"; if indexw ( refer_list , city2 ) > 0 then city_a=city2; else if indexw ( refer_list , city1 ) > 0 then city_a=city1; else city_a = "ROC"; run; ... View more

The "full volume" test would understandably fail considering the SAS limit on macro variable length. There is a macro sample at http://support.sas.com/kb/39/605.html?utm_source=dlvr.it&utm_medium=twitter that illustrates how to create a string that goes beyond the 64K limit for macro variables. Also, results that are heavily dependent on macro variables would be affected by settings such as MSYMTABMAX and MVARSIZE. ... View more

This appears to be a variation of a method published by Greg Mast in a paper titled "Managing Disk Space with SAS" (http://www2.sas.com/proceedings/sugi22/TRAINING/PAPER325.PDF). There are probably other versions "out there" as well. A close read of the document might be a good place to start. ... View more

Chang C. Chung has published a paper called "Page X of Y with Proc Report" (http://changchung.com/download/pageXofY_draft.pdf) that details a macro method of doing this. You should be ablet to adapt the macro (starts on page 3) to work with your by groups. Another option would be to figure out the number of rows that will fit on one page, and then create a separate "numpages" var for each by group (increment by one whenever the count of obs for a bygroup exceeds the number of rows that will fit on a page). The obvious disadvantage of this approach is that it is dependent on set font sizes, spacing, page sizes, etc. Yet another option would be to output a separate PDF file for each by group, which would require moving your ods pdf file statement inside the do block. Example (fictional variable called dummy used for illustration): %macro pdfrep; %do i=1 %to &tot.;   options pageno=1;   ods pdf file="sample&&grpid&i. .pdf" notoc ;   proc report data=group_prov1 nowd style={rules=none frame=void};      *by group_id;      where group_id=&&grpid&i.;      column   group_id dummy;      define group_id / group "group" ;   define dummy / order ;      title j=c "sample" ;      footnote j=r "Page ~{thispage} of ~{lastpage}";   run;   title;   footnote; %end; ods pdf close; %mend; %pdfrep; ... View more

It would be helpful to have a decent sample of the input data with a bit of a data dictionary, and a layout of what you want the desired output to look like (with definitions of calculated elements). ... View more

Is EVERY instance of Numerator and Denominator coming up zero? Have you confirmed that that crossings that should not be zero are returning zeros? ... View more