topic Re: how to input missing value in SAS Programming

how to input missing value

HeatherNewton — Tue, 30 Aug 2022 09:55:25 GMT

Data customer_h;     
input Obs org_code cust_no ind_vip cust_id id_type industrial_code occu_code dob_relation_level 
batch_date last_update_type last_update_datetime eff_from_date eff_to_date POSTAL_CODE RACE STATE_CODE NATIONALITY NID_ISSUE_COUNTRY;     

datalines;  
37086208 251 HK6C000000000000005 0 Q1234 1 191 21 1-Nov-78 2-Aug-22 U 03aug2022:22:41;57 2-Aug-22 . . . 2 7202816289 HK

I have three fields without input, how do I indicate when creating the data set?

the three fields without inputs are eff_to_date POSTAL_CODE RACE as indicated by '.' above...

Re: how to input missing value

bigdataguy — Tue, 30 Aug 2022 10:32:41 GMT

Perhaps try using infile statement and comma delimited values:

data customer_h;
infile datalines dsd delimiter=',';
input Obs org_code cust_no ind_vip cust_id id_type industrial_code occu_code dob_relation_level
batch_date last_update_type last_update_datetime eff_from_date eff_to_date POSTAL_CODE RACE STATE_CODE NATIONALITY NID_ISSUE_COUNTRY;
datalines;

37086208,251,HK6C000000000000005,0,Q1234,1,191,21,1-Nov-78,2-Aug-22,U,03aug2022:22:41;57,2-Aug-22,.,.,.,2,7202816289,HK

;

Re: how to input missing value

andreas_lds — Tue, 30 Aug 2022 10:58:03 GMT

The dot means missing numeric value.

Re: how to input missing value

bigdataguy — Tue, 30 Aug 2022 11:29:35 GMT

you need to use infile statement and dlm. You can use comma.

Have a look here, particularly the first example.

https://documentation.sas.com/doc/en/vdmmlcdc/8.1/lestmtsref/n1rill4udj0tfun1fvce3j401plo.htm

Re: how to input missing value

Kurt_Bremser — Tue, 30 Aug 2022 12:34:24 GMT

A single dot can be used to indicate a missing value, both for numeric and character variables:

data test;
input c1 $ c2 $ n1 n2;
datalines;
. xxx . 2
;

proc print data=test noobs;
run;

Result:

c1	c2	n1	n2
 	xxx	.	2

Re: how to input missing value

Tom — Tue, 30 Aug 2022 12:48:59 GMT

Looks like you have handled the missing values fine. INPUT will treat a single period as missing for both numeric and character variables.

It is the date, datetime and character variables that you have not told the INPUT statement how to handle correctly.

Plus for the datetime value you might need to read in as character initially so you can capture the strange ;57 at the end of the value and decide what to do with that.

input 
 Obs 
 org_code 
 cust_no i:$30.
 nd_vip cust_id :$10.
 id_type 
 industrial_code 
 occu_code 
 dob_relation_level :date.
 batch_date :date.
 last_update_type :$8.
 last_update_datetime :datetime.  
 eff_from_date :date. 
 eff_to_date :date. 
 POSTAL_CODE :$10.
 RACE :$20. 
 STATE_CODE :$8. 
 NATIONALITY :$10. 
 NID_ISSUE_COUNTRY :$8.
;     
format dob_relation_level batch_date eff_from_date eff_to_date date9.
       last_update_datetime datetime19.
;