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!
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;
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;
Thanks for the quick answer, I was hoping to not get the warning at all but this works and saves a step. Thank you!
@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. ...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.