topic Re: Spaces in a string variable in a .dat file causing column/truncating issue in SAS Programming

Spaces in a string variable in a .dat file causing column/truncating issue

Samurai — Thu, 04 Jun 2020 04:54:42 GMT

Hi,

The final two columns are causing a problem with my import. The full "room type" name is not appearing, only a small portion (if any) and the room rate column (final column) is sometimes appearing in the "room type" column. I believe the issue is tied to the spaces in the room type column and I am not coding appropriately to handle them. Any recommendations? I have searched a ton, and the options I have found have not worked.

Current code is:

DATA Hotel;
INFILE 'E:\Users\skm190000\Desktop\Datasets\Hotel.dat';
informat
roomnumber 4.
guests$ 1.
CI_month 1.
CI_day 2.
CI_year 4.
CO_month 1.
CO_day 2.
CO_year 4.
Internet$ 3.
DaysInternetUse 2.
RoomType$ 13.
RoomRate 3.
;

iNPUT
roomnumber
guests$
CI_month
CI_day
CI_year
CO_month
CO_day
CO_year
Internet$
DaysInternetUse
RoomType$
RoomRate
;

RUN;

I changed the file from .dat to txt for this request. so code above will have a shift in the file name.

Re: Spaces in a string variable in a .dat file causing column/truncating issue

Samurai — Thu, 04 Jun 2020 05:02:24 GMT

Re: Spaces in a string variable in a .dat file causing column/truncating issue

ChrisNZ — Thu, 04 Jun 2020 05:27:30 GMT

You also have an issue with the third last column being missing without any delimiter.

Since you almost have a column-aligned data, something like this might work:

input
    ROOMNUMBER
    GUESTS $
    CI_MONTH
    CI_DAY
    CI_YEAR
    CO_MONTH
    CO_DAY
    CO_YEAR
    INTERNET $
@49 DAYSINTERNETUSE 3.
    ROOMTYPE & $
    ROOMRATE
;

@49 positions the pointer to force reading at that position even if there is no data.

& tells SAS that a single space does not mark the end of the string, and that a double space must be found.

Re: Spaces in a string variable in a .dat file causing column/truncating issue

Samurai — Thu, 04 Jun 2020 06:00:22 GMT

Thank you! This will work. I am going to play around more to see if I can get the full room type name to appear, but this fixes my data placement issues! I really appreciate you taking the time to guide me!

Re: Spaces in a string variable in a .dat file causing column/truncating issue

ballardw — Thu, 04 Jun 2020 06:59:14 GMT

Do you know if this file has any history of editing or conversion?

A fair number of applications that use the .DAT extension create TAB delimited text, which this isn't. But if someone did a search an replace on tab characters replacing them with spaces (2 is my guess) you might get something like this.

Re: Spaces in a string variable in a .dat file causing column/truncating issue

Kurt_Bremser — Thu, 04 Jun 2020 08:05:15 GMT

Two positions fixed it for me:

data want;
input
  roomnumber
  guests $
  CI_month
  CI_day
  CI_year
  CO_month
  CO_day
  CO_year
  Internet $
  @49 DaysInternetUse 3.
  @53 RoomType $16.
  RoomRate
;
datalines;
211    3    2  7  2014    2  11  2014    NO         Deluxe Suite    295
214    2    2  2  2014    2  12  2014    NO         Basic no view    75
216    4    2  2  2014    2  13  2014    NO         Suite           255
220    5    2  3  2014    2  12  2014    YES    2    Basic w/view   155
221    3    2  3  2014    2  12  2014    NO         Luxury          195
223    5    2  7  2014    2  13  2014    NO         Suite           255
238    4    1  31  2014    2  13  2014    YES    10    Basic w/view 155
241    1    2  1  2014    2  13  2014    YES    3    Luxury         195
244    5    2  3  2014    2  12  2014    YES    9    Deluxe Suite   295
247    4    2  7  2014    2  11  2014    YES    4    Basic no view   75
248    4    2  8  2014    2  13  2014    YES    5    Basic no view   75
253    3    2  2  2014    2  12  2014    YES    7    Suite          255
255    5    2  8  2014    2  13  2014    NO         Basic w/view    155
270    1    2  4  2014    2  12  2014    NO         Deluxe Suite    295
272    2    2  2  2014    2  11  2014    YES    7    Suite          255
275    1    2  2  2014    2  10  2014    NO         Basic no view    75
276    5    2  10  2014    2  12  2014    NO         Basic w/view   155
281    1    2  9  2014    2  10  2014    NO         Basic w/view    155
283    1    2  8  2014    2  11  2014    NO         Deluxe Suite    295
288    3    2  2  2014    2  13  2014    NO         Suite           255
293    4    2  7  2014    2  11  2014    YES    4    Basic no view   75
294    5    2  1  2014    2  12  2014    YES    3    Basic w/view   155
298    6    2  1  2014    2  10  2014    NO         Suite           255
;