topic Re: How to prevent sas from filling missing data with the wrong column data in SAS Programming

How to prevent sas from filling missing data with the wrong column data

Errant — Tue, 29 May 2018 21:24:42 GMT

Hi y'all,

I have a raw dataset that represents hotel transactions. The yes/no column represents if a customer used the internet and the following numeric column represents days of internet use.

I used this code to input the data:

Data HotelA;
Infile 'C:\user\hotel.dat' missover;
Input 
room_number 
guest_number 
month_in 
day_in 
year_in 
month_out 
day_out 
year_out 
Internet_use $ 
Days_internet 
room_type & :$14.
Room_rate;
proc print data = HotelA;
Run;

When I run this code I get this output:

As you can see, when there is no value for days of internet use, it starts pulling data from the following column.

It's not pulling data from the wrong line, it's just pulling it from the wrong column in the same observation. I've tried using an if-then statement to exclude the numeric values, but it didn't resolve the underlying problem.

If room_type>0 then room_type = ' ';

I also tried truncover instead of missover and that somehow looked uglier with the same issue. How do you stop sas from filling in missing data with other columnar data within the same observation?

Thank you for your time/help!

- D.

Re: How to prevent sas from filling missing data with the wrong column data

PGStats — Tue, 29 May 2018 21:34:39 GMT

Try

Internet_use :$3.

days_internet 4.

in your input statement.

Re: How to prevent sas from filling missing data with the wrong column data

Reeza — Tue, 29 May 2018 22:39:56 GMT

Try adding the DSD option. This assumes that you have an extra blank space if you have an empty value. If you don't have an empty space, how is a missing value identified?

Re: How to prevent sas from filling missing data with the wrong column data

Astounding — Tue, 29 May 2018 22:40:43 GMT

If at all possible, you need a more rigorous INPUT statement ... one that tells SAS where to find the values. For example, you might begin with:

Input 
room_number    1-3
guest_number     4
month_in       6-7
day_in        9-10

Actually tell SAS which columns of the incoming file to read, to find each variable. The column numbers in this example are made up, because I can't actually see the incoming data.

If you can post a few lines of the incoming data, it would help get a better answer. Without that, you can try improving the current INPUT statement by changing one line:

Days_internet 3.

The dot at the end signifies that these are instructions ("read the next 3 characters") rather than the specific columns to be read. For it to get the right result, there have to be blanks in the data where DAYS_INTERNET would have appeared. At any rate, post a few lines of data to get a better answer.

Re: How to prevent sas from filling missing data with the wrong column data

Errant — Tue, 29 May 2018 23:37:17 GMT

Thank you! So far I've gotten the best results with this method.