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.
$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=.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.