topic Exclude Records when using Infile Statement in SAS Programming

Exclude Records when using Infile Statement

ddavies — Wed, 12 Feb 2020 14:04:35 GMT

I want to exclude records where the question_caption is "indicate relationship" because the score_value is NULL. When I use the infile statement as is below, I get a warning that there is invalid data for score_value for those records.

data outcome;
infile 'filepath\formname.csv' dlm=',' dsd firstobs=1 lrecl=1000;
input 
id :$50.
event_name :$50.
test_header_name :$50.
question_caption :$75.
score_value 
answer_value :$50.;
run;

Currently I am just using a data step (below) after that to exclude those records. But I would preference not bringing them in at all.

data outcome_b;
	set outcome;
	where question_caption notin ("Indicate relationship");
run;

Is there a way in my infile statement to exclude these records?

Thanks in advance!

Re: Exclude Records when using Infile Statement

Tom — Wed, 12 Feb 2020 14:08:50 GMT

No. you cannot use the INFILE statement to filter the data. But you can tell the data step with the INFILE statement to filter the data after you have read it (so you have something to test!).

data outcome;
  infile 'filepath\formname.csv' dlm=',' dsd firstobs=1 lrecl=1000;
  input 
    id :$50.
    event_name :$50.
    test_header_name :$50.
    question_caption :$75.
    score_value 
    answer_value :$50.
  ;
  if question_caption not in ("Indicate relationship");
run;

Re: Exclude Records when using Infile Statement

ddavies — Wed, 12 Feb 2020 14:13:32 GMT

Thanks for the quick answer, I was hoping to not get the warning at all but this works and saves a step. Thank you!

Re: Exclude Records when using Infile Statement

Tom — Wed, 12 Feb 2020 14:24:06 GMT

@ddavies wrote:

Thanks for the quick answer, I was hoping to not get the warning at all but this works and saves a step. Thank you!

Are you saying the CSV file you are reading someone typed the letters NULL when the value of SCORE was missing?

You can use format modifiers to suppress ALL error messages when reading SCORE.

input ... score ??  ... ;

Or create a custom informat that maps the string NULL to whatever value you want. You could map it missing or a special missing for example.

proc format;
invalue null 'NULL'=.N ;
run;
....
input ... score :null32. ...