Hi:
PROC FORMAT is a way to link a display value with a data value. So, for example, if the data has the codes 1 and 2 for Gender, you might want them to display as "M" and "F" in any report you create. A user-defined format will be the mechanism for how that happens. A PROC FORMAT list is a lookup list. So, from that standpoint (as a lookup list), a user-defined format can be used to select records. Sometimes this table lookup method -- with formats -- is very efficient; sometimes it is more efficient to use an SQL join or a merge. The papers below all offer some information on using PROC FORMAT and talk about TABLE lookups.
http://www2.sas.com/proceedings/sugi26/p158-26.pdf
http://www2.sas.com/proceedings/sugi26/p062-26.pdf
http://www2.sas.com/proceedings/sugi31/249-31.pdf
http://www2.sas.com/proceedings/sugi31/249-31.pdf
http://www2.sas.com/proceedings/forum2007/116-2007.pdf
One possible way to run a SAS procedure, is with a WHERE statement, such as this:
[pre]
proc means data=mybigfile n sum mean;
var height;
class age;
where gender = 1;
run;
[/pre]
So this PROC MEANS will run ONLY on the subset of data which satisfies the WHERE criteria. The PROC MEANS could be modified to use a more complex WHERE clause, but all the variables being tested must exist in the data set being sent to the procedure. So, if the MYBIGFILE data set contains a variable called "ID" then I could use ID in a WHERE statement. If MYBIGFILE did NOT have a variable called ID, then I could not use ID in a WHERE statement. In other words, if I wanted to do this:
[pre]
where GENDER = 1 and ID in (1, 2, 3, 4);
[/pre]
Then both GENDER and ID need to be in the data set being passed to PROC MEANS, or any procedure.
SAS has many procedures that are used for counting and summing records. You have posted this same SQL join question in several forums. It seems like you're searching for a solution -- either a hash table approach, a PROC TABULATE approach, a table lookup approach, or some approach that will approximate what you can express in the SQL join.
These forum postings on TABLE lookup may help you:
http://support.sas.com/forums/thread.jspa?messageID=3292
http://support.sas.com/forums/thread.jspa?messageID=8346ₚ
http://support.sas.com/forums/thread.jspa?threadID=2097&tstart=0
Your best bet for help with this question may be to contact Tech Support, they can help you figure out the best, most efficient approach, given the size of your input files, whether they're stored in SAS tables or in another database format and your particular processing needs. If all you need is a simple sum and count report, then they will probably be able to help you quicker than if you post the same query to different forums.
To send a question to Tech Support, go to
http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem".
cynthia