BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
2 REPLIES 2
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 673 views
  • 0 likes
  • 2 in conversation