DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

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

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. 

 

 HotelRaw.PNG

 

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:

Hotelout.PNG

 

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.


Accepted Solutions
Solution
‎05-29-2018 07:37 PM
Esteemed Advisor
Posts: 5,540

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

Try

 

Internet_use :$3.

days_internet 4.

 

in your input statement.

PG

View solution in original post


All Replies
Solution
‎05-29-2018 07:37 PM
Esteemed Advisor
Posts: 5,540

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

Try

 

Internet_use :$3.

days_internet 4.

 

in your input statement.

PG
Contributor
Posts: 24

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

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

 

 

 

Super User
Posts: 23,773

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

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?
Super User
Posts: 6,785

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 117 views
  • 0 likes
  • 4 in conversation