topic Re: How to treat delimiter in SAS Programming

How to treat delimiter

Guptashwe — Mon, 11 Mar 2019 10:55:52 GMT

Hi All,

I have the data as follows:

data test;
infile datalines MISSOVER dlm='|' dsd;
input YR_MTH_NBR YR_NBR MTH_NBR FAC_ID GST_TYP ENTERPRISE_ID STAY_GST_ID MEMBER_TYPE $
ENROLL_STY_IND $ SVY_SENT_DT mmddyy10. SVY_COMPLT_DT: mmddyy10. BRAND_MINOR_CD $
REGION_DESC $ MAIL_IND LANG_TYP $ CK_IN_DT: mmddyy10. CK_OUT_DT: mmddyy10.
VERB_1a_v: $ 100. Confirmation_number: HBR1_Region: $ HBR2_SubRegion: $ HBR3_Country: $
BK_SOURCE_CD: $ @;
format SVY_SENT_DT mmddyy10. SVY_COMPLT_DT mmddyy10. CK_IN_DT mmddyy10. CK_OUT_DT mmddyy10.;
datalines;
201612|2016|12|9278|1|640242220|2286593523|CLUB|N|12/31/2016|12/31/2016|HIIS|AMERICAS|1|ENU|12/28/2016|12/29/2016|The cooked breakfast option was wonderful. It was nice to order from a menu vs eat from a hot/cold breakfast buffet bar.Room was clean and comfortable and plenty of space. We had restful sleep.|62099040|Americas|North America|United States|CRO

201612|2016|12|10822|1||2274029967|SPRE|N|12/03/2016|12/31/2016|HEXS|AMERICAS|4|ENU|11/30/2016|12/01/2016|Better quality breakfast food.|60958042|Americas|North America|United States|LMNCO
;
run;

Concern:

1) I am not able to read the correct values.

2) The above code is not treating two consecutive delimiters as missing value.

Kindly suggest a code which can read through above values

Re: How to treat delimiter

s_lassen — Mon, 11 Mar 2019 11:08:23 GMT

You forgot a colon after SVY_SENT_DT, that's all!

Re: How to treat delimiter

ballardw — Mon, 11 Mar 2019 22:33:11 GMT

Also your example has much more than 100 characters for the variable VERB_1a_v.

The variables HBR2_SubRegion and HBR3_Country are also truncated because the informat isn't long enough.

If your full data has more variables that appear incomplete you likely need to adjust the informat(s) to read more characters.

Re: How to treat delimiter

Tom — Mon, 11 Mar 2019 21:06:26 GMT

It is the LENGTH and not the format is that will cause truncation for those two character variables. Formats just effect how a variable is displayed. Since no formats are attached the format cannot cause any truncation.

Perhaps you meant the informat? But when reading using LIST mode SAS will ignore the width on the informat specification. So even if there was too short a width on the informat it wouldn't cause truncation.

However since no length for the variable has been set SAS will need to decide on its own how to define the length. And since there is also no informat with its associated width for SAS to base its guess on it will use the default length of 8 characters (bytes actually).

Re: How to treat delimiter

ballardw — Mon, 11 Mar 2019 22:32:16 GMT

@Tom wrote:

It is the LENGTH and not the format is that will cause truncation for those two character variables. Formats just effect how a variable is displayed. Since no formats are attached the format cannot cause any truncation.

Perhaps you meant the informat? But when reading using LIST mode SAS will ignore the width on the informat specification. So even if there was too short a width on the informat it wouldn't cause truncation.

However since no length for the variable has been set SAS will need to decide on its own how to define the length. And since there is also no informat with its associated width for SAS to base its guess on it will use the default length of 8 characters (bytes actually).

Correct and I mentioned the informat in the second bit. Edited above.