topic Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value. in SAS Programming

Infile statement is converting . (dot) to a blank and I need to preserve the . value.

NJGIRL — Tue, 11 Aug 2020 20:28:18 GMT

Hello,

I am looking or a sample infile statement that will read in a csv file and preserve a value of . (dot) NOT convert the . to a blank.

Thanks!

data raw;

infile "\\dfs\data\SAS\data\import\XXX\&file."

dlm=';' dsd lrecl=32767 encoding=any missover;

informat var1-var14 $200. var15 $500. var16-var19 $200.;

input var1-var19 $;

run;

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

SASKiwi — Tue, 11 Aug 2020 20:47:04 GMT

A dot denotes the end of a macro variable. Put two consecutive dots if you want a single dot to be retained.

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

ballardw — Tue, 11 Aug 2020 20:55:09 GMT

It isn't really clear if you mean the values of the variables or the name of the file.

I would consider naming files with a . at the end to be a marginally poor practice. The dot typically is used as separator from an extension and if there is no extension in the file name then the proper reference is to not have .

I hope you aren't getting confused about your actual file name because you have "show extenstions" turned off in any file manager program you use.

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

NJGIRL — Tue, 11 Aug 2020 20:55:12 GMT

Thank you, however, I know that &file. is a macro variable. ( I should not have included the macro variable in the example)
I am referring to the infile statement itself -- it is converting var10 value in to a blank when the value = . and I need to retain the . (dot) because . is a valid value. I

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

NJGIRL — Tue, 11 Aug 2020 21:10:11 GMT

oh gosh, I should not have included the infile macro file name in the post as it has nothing to do with the issue.. just to clarify, we don't have the file name ending in a dot 🙂 Just for clarification, the preceding code is

/* Bring in External Data */

%let file = xxxx_Current.csv;

sorry for the confusion.

Anyway, back to the issue -- Var10 has a . (dot) as a valid value in the csv file and I need to retain the . (. means a lab value is in normal range)

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

ballardw — Tue, 11 Aug 2020 21:26:42 GMT

@NJGIRL wrote:

oh gosh, I should not have included the infile macro file name in the post as it has nothing to do with the issue.. just to clarify, we don't have the file name ending in a dot 🙂 Just for clarification, the preceding code is

/* Bring in External Data */

%let file = xxxx_Current.csv;

sorry for the confusion.

Anyway, back to the issue -- Var10 has a . (dot) as a valid value in the csv file and I need to retain the . (. means a lab value is in normal range)

Custom informats can handle this though it may be inconvenient.

proc format;
invalue $c_ (max=1000)
'.' = '.'
;

data example;
  infile datalines dlm=';';
  input x :$c_50.  y;
datalines4;
sometext;25
.;37
some much longer text to check if 50 is actually used;18
;;;;

I set the max length to this informat to 1000 characters, if you need it longer replace the MAX= value but there is still the single variable limit of 32767. Then specify the needed length as with the $ format but use $c_ (or pick another name in the format statement).

The last line of example data will cut off the value at 50 characters per the informat so the 'sed' part of the text does not appear in variable X.

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

Tom — Tue, 11 Aug 2020 22:49:22 GMT

It has nothing to do with the INFILE statement. If you want SAS to interpret a single period as other than missing when reading character data then you need to use the $CHAR informat instead of the default $ informat. Note that this will also preserve leading spaces, however DSD option should remove the leading spaces so you don't need to worry about that.

data raw;
  infile "...."  dlm=';' dsd lrecl=32767 encoding=any truncover ;
  length var1-var14 $200 var15 $500 var16-var19 $200;
  input var1-var19 ;
  informat var1-var19 $char.;
run;

NOTES: Don't use INFORMAT (or FORMAT) to define the variables. Instead actually tell SAS how you want them defined by using LENGTH (or ATTRIB) statement. When using DSD you don't need to include a width on the INFORMAT (especially if you are not misusing the informat statement to define the variable) since when using list mode input the width of the informat is ignored. TRUNCOVER is generally better than MISSOVER because of what happens to a value at the end of the line that is shorter than the informat width, but that doesn't make any difference when you list mode input (see early statement about ignoring informat width).

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

NJGIRL — Wed, 12 Aug 2020 13:58:12 GMT

Thank you Tom for your input (no pun intended:) Yep, I do know the issue is not with the Infile stmt itself.
I will try the $CHAR format this morning, It is very helpful that you provided code.
I like the idea of adding the length statement to datastep #1.BTW, here is datastep #2. If you have any further comments, they are most welcome.

data XXX1
   length study $11 barcode $10 yr_birth $11 gender fasting $1 parmcd $6 parmnm lbcat result $50 class $1
          range $30 lim_lo lim_hi $8 unit $30 comment $500 subject $6 visit $20 sampdate samptime 4;       set XXX_raw(firstobs=2);

   study   = strip(var1);
   barcode = strip(var2);   etc, etc.
Thanks! NJGIRL

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

Tom — Wed, 12 Aug 2020 14:06:16 GMT

You latest example is not using INPUT statement or INPUT() function so it will not have the issue you are talking about.

Either the periods are in the variables being set from the exist dataset or they aren't. You haven't shown any code to remove the periods or any code that would create values with period.

Re: Infile statement is converting . (dot) to a blank and I need to preserve the . value.

NJGIRL — Wed, 12 Aug 2020 21:22:26 GMT

I only displayed the 2nd datastep just for informational purposes so you could see where I was going with the code and to point out that I was establishing the lengths in the 2nd datastep, not the 1st.

All is good. The $char successfully read in the variable in question and properly preserved the '.' (period) . This is a handy format, that, quite frankly, I had forgotten about. This saved a lot of time and I appreciate your quick response.

take care.