How to access the link in an external look up table?

deleted_user — Thu, 08 May 2008 12:59:41 GMT

I want to range check a dataset and have an external lookup table containing variable names and links to where their valid values are stored.

errorvar lookupfile
state /c:/myfile/state.csv
gender /c:/myfle/gender.csv
...

I want to retrieve the valid values stored in those lookup files and compare those values with my data. I want to create an indicator which equals "yes" when there is a match and "no" when there is no match.

Can anybody help me find a solution? Thanks a million.

Re: How to access the link in an external look up table?

deleted_user — Thu, 08 May 2008 14:47:18 GMT

This will involve FILENAME statements and FILENAME functions, and may require use of FETCH or FETCHOBS, or not.

You may want to look into CALL EXECUTE and look into some macro programming.

An easier way may be to use PROC SQL and simply use some will constructed joins.

It all depends on how you want to apply the range checking.

Re: How to access the link in an external look up table?

Cynthia_sas — Thu, 08 May 2008 15:52:45 GMT

Hi:
Depending on how often the values in the CSV files change, you may be able to use the features of PROC FORMAT to create a user-defined format from your CSV files...

1) read the CSV file into SAS
2) create a dataset tailored to PROC FORMAT CNTLIN
3) run a PROC FORMAT step using the dataset in #2
4) then the new format is available for doing the kind of recoding you want -- you can build the format on a permanent basis if the data doesn't change much or you can build the format each time you run the job that does the validation checking.
5) repeat #1, 2 and 3 for every one of your variables -- probably in a SAS Macro program -- as Chuck indicated.

There's an example at the end of this post that uses SASHELP.SHOES as an example...to create 1 format for Product and create a new variable with the format.

If your data changes hourly or daily, this might not be the preferred approach. but if your CSV files only change weekly or occasionally, then you might be able to work a CNTLIN/PROC FORMAT example into a SAS Macro program solution that would update the permanent formats when the CSV files changed. OR you could set up a periodic refresh.

cynthia
[pre]

** make some data to test;
** You would read your CSV file here;
** Only Products listed here will be coded with 'Yes';
data file1;
length Product $14;
infile datalines dlm=',' dsd;
input Product $ ;
return;
datalines;
"Boot"
"Men's Casual"
"Men's Dress"
"Women's Casual"
"Women's Dress"
;
run;

ods listing;

** make a user-defined format for the products;
Data fmt_from_data;
set work.file1(rename=(product=start)) end=eof;
Retain FMTNAME "PRODF"
TYPE "C";
Length LABEL $60;
Label = "Yes";
output;
if eof then do;
hlo="O";
label="No";
output;
end;
Run;

Proc Format CntlIn = fmt_from_data fmtlib;
Run;

** start ODS;
ods listing close;
ods html file='c:\temp\useFmt.html' style=sasweb;

data makeflag;
set sashelp.shoes;
newflag = put(product,$PRODF.);
run;

proc print data=makeflag;
where region = 'Asia';
var region product newflag sales;
title 'Only Print One Region';
run;

** PROC FREQ;
proc freq data=makeflag;
title 'Using Format to Double Check Values for Product';
tables product newflag;
format product $PRODF.;
run;

title;
ods html close;

[/pre]

topic Re: How to access the link in an external look up table? in SAS Programming

How to access the link in an external look up table?

Re: How to access the link in an external look up table?

Re: How to access the link in an external look up table?