Help using Base SAS procedures

importing a text file - getting errors

Reply
New Contributor
Posts: 3

importing a text file - getting errors

Hi all -

This is a relatively simple question, but I for the life of me - and it's embarassign - cannot find a solution (yes, a newbie here).

I'm importing lists of order ids (a numerical variable) to work with in SAS. However, when reading in the files, I get an error message. Researching the lists, I realized some of the variables contain a character value - "NULL". I just cannot seem to find a way to delete values with "NULL" when reading in the file. I prefer to do this through SAS than manually through Excel in order to minimize possible errors. My question is - is there, when reading in numerical value, a way to delete values that don't meet the criteria (character values)? Here's the code I use to read in the file.

data ds_name;
infile '/export/SAS/users/jones//list_files//ds_name.txt'
delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=1;

informat order_id 11.;
format order_id 11.;
input order_id;

if order_id = . then delete;
run;

I also tried the following but got a log filled with error messages, and know it's because SAS is reading the values as numerical data:

data ds_name;
infile '/export/SAS/users/jones//list_files//ds_name.txt'
delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=1;

informat order_id 11.;
format order_id 11.;
input order_id;

if order_id = . then delete;
if order_id = 'NULL' then delete;
run;

Any help is appreciated! If you have any questions and or need clarification, don't hesitate to let me know. Thanks in advance.
Super Contributor
Posts: 578

Re: importing a text file - getting errors

Posted in reply to justchecking
How about:

data ds_name(drop=t_order_id);
infile '/export/SAS/users/jones//list_files//ds_name.txt'
delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=1;
length t_order_id $11 order_id 8;
format order_id 11.;
input t_order_id;
if t_order_id ne '' and t_order_id ne 'NULL';
order_id=input(t_order_id,11.);
run;
Regular Contributor
Posts: 165

Re: importing a text file - getting errors

Posted in reply to justchecking
If you want to avoid the notes about invalid character values then read the field in as character. You can then use the anyalpha() function to check for characters and delete them. A input() funtion can then convert the remaining values to numeric.

Good luck!
Respected Advisor
Posts: 3,799

Re: importing a text file - getting errors

Posted in reply to justchecking
> Hi all -
> character value - "NULL". I just cannot seem to find
> a way to delete values with "NULL" when reading in
> the file. I prefer to do this through SAS than
> manually through Excel in order to minimize possible
> data ds_name;
> infile
> '/export/SAS/users/jones//list_files//ds_name.txt'
> delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=1;
>
> informat order_id 11.;
> format order_id 11.;
> input order_id;
>
> if order_id = . then delete;
> run;

This should be working, look closely at the number of obs in the output data. It does however produce invalid data messages when the program reads order_id from a field that does not contain a proper numeric. Those messages can be easily suppressed with ?? for example.

[pre]
filename FT15F001 temp;
data ds_name;
infile FT15F001
/*'/export/SAS/users/jones//list_files//ds_name.txt'*/
delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=1;

informat order_id 11.;
format order_id 11.;
input order_id ??;

if order_id = . then delete;
parmcards;
null
100000
20000
null
NULL
200303
;;;;
run;
[pre]
New Contributor
Posts: 3

Re: importing a text file - getting errors

Posted in reply to data_null__
Everyone -

Thank you so much for your responses! data_null_; - you were right; the number of obs in the output data was correct (the NULL values were never uploaded) - I must have been too distracted by the error messages to see this. Like you said, the use of ??s suppressed the error messages - and my worries.

Again, thank you everyone!
Ask a Question
Discussion stats
  • 4 replies
  • 512 views
  • 0 likes
  • 4 in conversation