topic Re: Searching a SAS dataset while reading in a Flatefile in SAS Programming

Searching a SAS dataset while reading in a Flatefile

deleted_user — Fri, 09 Jan 2009 23:02:24 GMT

I have a situation in which I need to read in an extremely large flatfile (on multiple tapes) into my SAS program. I need to compare the zipcodes in this flatfile with a list of zipcodes I have in a SAS file. If the zipcode in the input file appears on the SAS zipcode file, I want to keep that record for additional processing.

I was hoping to be able filter out the records I do not what while I am reading them and not read in the entire file into a SAS dataset to then determine which records a what to keep. Something like:

data In_File;
infile MASTER Length=Recl TRUNCOVER;
input @001 zip $ 5. @;
If zip is in ZIPCODE file, then keep record;
run;

Any thoughts on how to do this in the Data Step that reads in the large file?

Thanks

Re: Searching a SAS dataset while reading in a Flatefile

sbb — Fri, 09 Jan 2009 23:56:51 GMT

Consider using SAS PROC FORMAT to build a look-up data table, and you would use the SAS PUT function in your DATA step to determine if the input-file data contents is contained in your selection criteria (from your SAS file, after it has been converted to the SAS format). A SAS file can be used as input to the PROC FORMAT procedure, using the CNTLIN= keyword, when the SAS file is populated with specific-named SAS variables (FMTNAME, HLO, START, LABEL, at a minimum).

You can use the SAS support website http://support.sas.com/ to find SAS-hosted product documentation and also SAS user community technical papers on this topic.

Scott Barry
SBBWorks, Inc.

Creating a Format from Raw Data or a SAS® Dataset
http://www2.sas.com/proceedings/forum2007/068-2007.pdf

SAS FORMAT PROCEDURE
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/a000063536.htm

Re: Searching a SAS dataset while reading in a Flatefile

LinusH — Mon, 12 Jan 2009 07:57:07 GMT

You could also consider to use SET with the KEY option, if you are having your zipcode table indexed. Another approach could to read the file with a data step view, then do an inner join to the zipcode table.

/Linus

Re: Searching a SAS dataset while reading in a Flatefile

deleted_user — Wed, 21 Jan 2009 01:31:13 GMT

Thanks for the assistance "sbb". You suggestion worked perfectly. Great SAS technique!

Re: Searching a SAS dataset while reading in a Flatefile

deleted_user — Wed, 21 Jan 2009 02:11:22 GMT

You may want to create a format from your small SAS file. Your label can consist of 'matchyes' or 'matchno'. 'matchno' would have a value of 'other'. Then add a where statement when running against the large file, "where zipcode = 'matchyes'".