topic Re: Reading a missing value from a CSV file in SAS Programming

Reading a missing value from a CSV file

dsadsad — Thu, 12 Mar 2020 19:58:05 GMT

Hi ,

I am reading a missing value dot(.) for one of the variable(Charecter) in csv file. SAS is converting it as blank ('') in the output dataset.

How can i preserve dot(.) in the output .

Below are the details.

csv file :

12345|2-K|Mr.|David|WHITE|.|Y|09/03/2018|.|Australia/Sydney

Program

data work.W5RD4PZ5 ;
infile "/sas/saswork/nag/CONTACTS_20200313_01.csv" lrecl = 1024 delimiter = '|' dsd missover firstobs = 1;
attrib ID length = $30 format = $30. informat = $30.;
attrib CONTACTREF length = $20 format = $20. informat = $20.;
attrib TITLE1 length = $20 format = $20. informat = $20.;
attrib GIVENNAME length = $100 format = $100. informat = $100.;
attrib FAMILYNAME length = $100 format = $100. informat = $100.;
attrib COMPANYNAME length = $200 format = $200. informat = $200.;
attrib ISEXTERNALLYMAINTAINED length = $1 format = $1. informat = $1.;
attrib LASTMODIFIED length = $20 format = $20. informat = $20.;
attrib MIDDLENAME length = $100 format = $100. informat = $100.;
attrib TIMEZONE1 length = $40 format = $40. informat = $40.;
input ID CONTACTREF TITLE1 GIVENNAME FAMILYNAME COMPANYNAME ISEXTERNALLYMAINTAINED LASTMODIFIED MIDDLENAME TIMEZONE1;
run;

Output :

From the above file , Variables Companyname and Middlename are having values as dot(.) but in the ouput dataset it is showing as blank(''). How can we retain (.) in the output .

Thanks in Advance.

Re: Reading a missing value from a CSV file

data_null__ — Thu, 12 Mar 2020 20:16:41 GMT

$CHAR in-format. However missing character variable should be blank not dot. Dot is not missing.

34         data _null_;
35            infile cards dsd;
36            input a:$char12. b c;
37            put (_all_)(=);
38            cards;

a=. b=1 c=2
a=abc b=. c=.

Re: Reading a missing value from a CSV file

ballardw — Thu, 12 Mar 2020 23:09:31 GMT

A typically constructed CSV file would have NO value for a 'missing'. The csv would look like

Abc,,123

To have the second value as missing.

SAS however will read a . as missing, as you have discovered.

data example;
   infile datalines dlm=',' dsd;
   informat x y z $5.;
   input x y z;
datalines;
abc,,456
brd,.,ghq
;

You can force reading the dot with a custom informat:

proc format library=work;
invalue $keepdot 
'.'='.'
other = _same_;
run;
data example;
   infile datalines dlm=',' dsd;
   informat x y z $keepdot3.;
   input x y z;
datalines;
abc,,456
brd,.,ghq
;

Caveat: there may be issues getting lengthy variables read as this doesn't use a default length. You could assign default length in the INVALUE statement for the longest actual expected variable value you expect the dots to occur in and then use the LENGTH attribute to keep from having the shorter values end up with longer values. There might actually be some playing around needed.

Of course is you have NO actual blanks in the data source you could assign the dot as the value, or create a format depending on use.