topic Re: Date Formating Causing ERROR value instead of missing in the data set in SAS Programming

Date Formating Causing ERROR value instead of missing in the data set

Ganeshk — Fri, 25 Jan 2019 11:41:07 GMT

Hi Team,

I used the proc format procedure on data set:

I am getting missing values as "ERROR" instead of "." missing value.

How to fix the issue, from "ERROR" to missing(.) value?

Thanks,

Ganesh K

Re: Date Formating Causing ERROR value instead of missing in the data set

PeterClemmensen — Fri, 25 Jan 2019 10:47:41 GMT

Why do you type %proc format?

Show us your code that generates data.

Re: Date Formating Causing ERROR value instead of missing in the data set

andreas_lds — Fri, 25 Jan 2019 10:47:57 GMT

Please post the log containing the execution of that proc format, i am 100% sure that you get an error message, complaining about "%proc".

Re: Date Formating Causing ERROR value instead of missing in the data set

RW9 — Fri, 25 Jan 2019 11:13:08 GMT

As @PeterClemmensen states, % invokes the macro pre-processor, it should never appear in front of the word proc which is a reserved word in SAS.

Re: Date Formating Causing ERROR value instead of missing in the data set

Kurt_Bremser — Fri, 25 Jan 2019 11:35:32 GMT

Fix this ERROR first:

15         %proc format;
           _
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Debugging needs to be done top-down. Fixing the first ERROR/WARNING in your code might prevent all others from happening.

Re: Date Formating Causing ERROR value instead of missing in the data set

Ganeshk — Fri, 25 Jan 2019 11:37:31 GMT

% was Typo error before taking snap shot. I checked Log, there is no error.

Actually problem is different, in output i am getting "ERROR" for missing value.

Re: Date Formating Causing ERROR value instead of missing in the data set

Ganeshk — Fri, 25 Jan 2019 11:39:21 GMT

No Error in Log. Its Typo before taking screen shot. Require Solution as posted above. could you please give some thought.

Re: Date Formating Causing ERROR value instead of missing in the data set

Kurt_Bremser — Fri, 25 Jan 2019 11:50:39 GMT

Please post code as text (see my footnotes) by simply copy/pasting from SAS to a window opened with the "little running man".

And supply example data in usable form, again see my footnotes. Help us to help you.

Your format works:

proc format;
picture dtpic
  other='%Y-%0m-%0d %0H:%0M:%0S ' (datatype=datetime);
run;

data test;
date1 = datetime();
format date1 dtpic.;
run;

proc print data=test noobs;
run;

Log of the data step:

42         data test;
43         date1 = datetime();
44         format date1 dtpic.;
45         run;

NOTE: The data set WORK.TEST has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Result:

       date1

2019-01-25 12:48:47

Re: Date Formating Causing ERROR value instead of missing in the data set

Ganeshk — Fri, 25 Jan 2019 12:59:39 GMT

Since the value for date exist, "ERROR" wont be depicted. What if the missing value exist in Second record. In my case i have missing value.

Re: Date Formating Causing ERROR value instead of missing in the data set

Kurt_Bremser — Fri, 25 Jan 2019 13:01:56 GMT

POST

WHOLE

CODE

AND

EXAMPLE

DATA

USABLE

FORM

Re: Date Formating Causing ERROR value instead of missing in the data set

Ksharp — Fri, 25 Jan 2019 13:03:36 GMT

proc format;
picture dtpic
  low-high='%Y-%0m-%0d %0H:%0M:%0S ' (datatype=datetime);
run;

data test;
date1=.;output;
date1 = datetime();output;
format date1 dtpic.;
run;

Re: Date Formating Causing ERROR value instead of missing in the data set

Ganeshk — Fri, 25 Jan 2019 13:18:21 GMT

Hi Ksharp,

Thanks for reply. Here is the sample data set which i have prepared for you:

data art;

input ID ID_C Date1 Date2;

datalines;

343 565 1.8362E9 .

3435 4546 . -3.592E9

;

proc format;

picture dtpic

other='%Y-%0m-%0d %0H:%0M:%0S ' ( datatype=datetime);

run;

data test;

set art;

format Date1 dtpic.;

format Date2 dtpic.;

run;

Please help me to resolve with above code.

Thanks,

Ganesh K

Re: Date Formating Causing ERROR value instead of missing in the data set

Kurt_Bremser — Fri, 25 Jan 2019 13:40:55 GMT

Just add a proper definition for missing values:

proc format;
picture dtpic
  . = ' '
  other = '%Y-%0m-%0d %0H:%0M:%0S'
  (datatype=datetime)
;
run;

data test;
input ID ID_C Date1 Date2;
format date1 date2 dtpic.;
datalines;
343 565 1.8362E9 .
3435 4546 . -3.592E9
;
run;

proc print data=test noobs;
run;

Result:

 ID     ID_C           Date1                  Date2

 343     565    2018-03-09 07:33:20                       
3435    4546                           1846-03-04 22:13:20

Re: Date Formating Causing ERROR value instead of missing in the data set

Ksharp — Fri, 25 Jan 2019 13:39:53 GMT

Did you see the difference part of my code with your code ?

low-high='%Y-%0m-%0d %0H:%0M:%0S '

Re: Date Formating Causing ERROR value instead of missing in the data set

RW9 — Fri, 25 Jan 2019 13:41:15 GMT

Probably not ideal, but I always have numeric and char representations of these types of things (in our industry models this is standard as well), so I would do:

data art;
  input id id_c date1 date2;
datalines;
343 565 1.8362E9 .
3435 4546 . -3.592E9
;
run;
 
proc format;
  picture dtpic
    other='%Y-%0m-%0d %0H:%0M:%0S ' ( datatype=datetime);
run;

data test;
  set art;
  if date1 ne . then char_date1=put(date1,dtpic.);
  if date2 ne . then char_date2=put(date2,dtpic.);
run;

I would question why you do not use T to separate date and time, as shown in standard - and that is international standard - ISO dates, e.g:

YYYY-MM-DDTHH:MM.SS

Re: Date Formating Causing ERROR value instead of missing in the data set

RW9 — Fri, 25 Jan 2019 13:44:09 GMT

Interestingly, that does not work for me.

data art;
  input id id_c date1 date2;
datalines;
343 565 1.8362E9 .
3435 4546 . -3.592E9
;
run;
 
proc format;
  picture dtpic
    . = ' '
    other='%Y-%0m-%0d %0H:%0M:%0S ' ( datatype=datetime);
run;

data test;
  set art;
  format date1 date2 dtpic.;
run;

proc print data=test;
run;

                                         The SAS System        13:43 Friday, January 25, 2019   1

            Obs     id     id_c    date1                      date2

             1      343     565        2018-03-09 07:33:20    ERROR                  
             2     3435    4546    ERROR                          1846-03-04 22:13:20

Re: Date Formating Causing ERROR value instead of missing in the data set

Kurt_Bremser — Fri, 25 Jan 2019 13:48:04 GMT

Interesting indeed. I'm on 9.4M5 on AIX, and get no ERROR for the missing values.

Re: Date Formating Causing ERROR value instead of missing in the data set

RW9 — Fri, 25 Jan 2019 13:53:26 GMT

Possibly a version issue then as I am running on:

9.04.01M3P062415

OS: LIN X64