INPUT MODIFIED LIST

[smart]

Here is my code. I do not have any errors, but I am having problems with zip code, address and vehicle (last 3 variables in input statement). How do I correctly fix my code so that these 3 variables show up correctly? Please help. Thank you in advanced. 

data November2;
length Firstname $7 Lastname $8; */used length statement to have names display first;
infile '/courses/ddb976e5ba27fe300/PSY6560/ExternalData/HotelOccupancyNov18_v2.dat';
input Checkindate:ANYDTDTE20. Checkoutdate:ANYDTDTE20.
roomrate:comma. FirstName:$7. LastName:$6.
zipcode:comma3. address &:$40. vehicle $;
format checkindate date10. checkoutdate date10. roomrate dollar12.2;
run;

datalines;
5-Nov-18 7-Nov-18 $105.00 Jordan Bailey 101 Wrong Way Alexandria TN 37012 N
3-Nov-18 4-Nov-18 $150.00 Sydney Ashton 210 Freeway Auburntown TN 37016 Y
3-Nov-18 6-Nov-18 $95.00 Taylor Darby 555 New Way Elkton TN 38455 N
31-Oct-18 . $105.00 Payton Finley 888 Final Way Englewood TN 37329 Y
2-Nov-18 5-Nov-18 $105.00 Spencer Greer 757 Airport Way Cold Spring KY 41076 Y
4-Nov-18 5-Nov-18 $100.00 Adrian Hayden 000 No Way Columbus KY 42032 Y
7-Nov-18 . $160.00 Casey Harper 666 Devil's Way Crestview Hills KY 41017 Y
;

run;

[ballardw]

What were you told about the content of '/courses/ddb976e5ba27fe300/PSY6560/ExternalData/HotelOccupancyNov18_v2.dat' ?

Is it a delimited file where commas, tabs or some other character separates values or is it fixed column? or something else.

If the file is supposed to be delimited then likely you only need is to provide the delimiter option on the infile statement such as

dlm=','   (comma separated) or dlm='09'x  (tab delimited since tab characters don't show will in code the hex equivalent is used).

One thing is if your zip code is 5 characters then using COMMA3 to read the data would at a minimum truncate the value to 3 characters.

I am afraid that datalines as pasted  no longer represents your example data if it ever did. The message window on this forum reformats text and so your datalines may not have all of the characters you had originally. Additionally typed datalines probably do not actually represent your file well. If you do not have a description of your DAT file you should paste a few lines from it directly into a code box opened using the forum's {I} icon. That box preserves text as pasted.

[FreelanceReinh]

Hello @smart,

If you want to read the data from the data lines, remove both the INFILE statement and the first RUN statement and proceed as described below:

1. ZIPCODE should be a character variable because it can have leading zeros and you won't do arithmetic with zip codes anyway. So, you may want to use :$3. instead of :comma3. as the informat specification for it (if you are sure that these codes have a maximum length of 3 as in your sample data).
2. It is good that you used the "&" modifier for ADDRESS because of the embedded blanks. Unfortunately, the last field (for variable VEHICLE) is separated from the address by only a single blank. This should be a double blank to make the modified list input work. Otherwise SAS doesn't recognize that the "N" or "Y" is not part of the address. (A case of "messy" raw data -- but possibly this is just a side effect of copying and pasting the data, as mentioned by ballardw.)
   
   One way to work around this is: Remove "vehicle $" from the INPUT statement to let SAS read the value for VEHICLE temporarily as part of the value of ADDRESS (make sure that $40. is still long enough to accommodate the two additional characters). Then insert the following statements after the INPUT statement:
   

   length vehicle $1;
   if length(scan(address,-1,' '))=1 then do;
     vehicle=scan(address,-1,' ');
     address=substr(address,1,length(address)-1);
   end;

   This assumes that VEHICLE has only values of length 1 and that ADDRESS is never missing.
   
   The inserted code defines VEHICLE as a character variable of length 1 and makes it the last variable of the dataset in logical order. The function call scan(address,-1,' ') yields the last (that's the -1) "word" of the string contained in ADDRESS using a blank (third argument of SCAN) as word delimiter. If the length of that "word" is 1, it is assumed that it is in fact the value for VEHICLE. Hence it is assigned to that variable and subsequently removed from variable ADDRESS by means of the SUBSTR function. (Note that the last argument of SUBSTR could equivalently be written as length(address)-2: It doesn't make a difference to cut off the blank before "N" or "Y" -- ADDRESS will be padded with trailing blanks anyway.)