BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Errant
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Try

 

Internet_use :$3.

days_internet 4.

 

in your input statement.

PG

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

Try

 

Internet_use :$3.

days_internet 4.

 

in your input statement.

PG
Errant
Obsidian | Level 7

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

 

 

 

Reeza
Super User
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?
Astounding
PROC Star

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.

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!

How to Concatenate Values

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.

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