BookmarkSubscribeRSS Feed
justchecking
Calcite | Level 5
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.
4 REPLIES 4
DBailey
Lapis Lazuli | Level 10
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;
RickM
Fluorite | Level 6
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!
data_null__
Jade | Level 19
> 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]
justchecking
Calcite | Level 5
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!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1471 views
  • 0 likes
  • 4 in conversation