help in my sql code

[knveraraju91]

Dear,

I am getting the below messages in the log when i run the pgm. Please suggest how to avoid the messages. Thank you

Invalid date value
NOTE: Invalid argument to function INPUT. Missing values may be generated.

data have;
input id date $16.;
datalines;
1 2019-12-17T13:30
1 2020-01-16T12:15
1 
1 2019-12-19T14:25
;

proc sql noprint;
create table want as
select distinct id,
case 
when not missing(date) then max(input(substr(date,1,10),is8601da.)) 
end as edt format = yymmdd10.
from have   
group by id
order by id;
quit;

[tomrvincent]

3rd record has no date.

[tomrvincent]

also, I think you want this for edt:

max(case
when not missing(date) then input(substr(date,1,10),is8601da.)
end) as edt format = yymmdd10.

[mbuchecker]

Hi,

I'm not really sure what your desired output is, but this code simplifies a lot of what you were attempting to do. I'm just not sure how you wanted to handle the missing value. In SAS, missing values are treated like negative infinity, so any date will be larger than the missing date.

data have;
infile datalines missover;
input id date : yymmdd10.;
datalines;
1 2019-12-17T13:30
1 2020-01-16T12:15
1 
1 2019-12-19T14:25
;
proc print; 
run;

proc sql noprint;
create table want as
select distinct id,
max(date) as edt format = yymmdd10.
from have   
group by id
order by id;
quit;

Michelle

[ChrisNZ]

You SQL could also be

proc sql ;
  create table WANT as
    select distinct ID
   ,max(input(DATE,?? is8601da.)) as EDT format = yymmdd10.
  from HAVE
  group by ID
  order by ID;
quit;

The width of informat is8601da. is 10.

The ?? takes care of bad dates.

Function max() will keep any date at all as missing dates are the smallest.

[Ksharp]

If I reckon right, ?? is invalid in SQL . ?? only work in Data step .

[ChrisNZ]

> If I reckon right, ?? is invalid in SQL . ?? only work in Data step .

Oh...  I didn't remember that and I can't test. I haven't had SAS for weeks now, still awaiting the deployment...

Any other function that behaves differently? I know some functions  cannot be used, such a peek or lag or symput, but different syntaxes is another level altogether.

[Ksharp]

Ha, Chris Here is the LOG.

 79         proc sql ;
 80           create table WANT as
 81             select distinct ID
 82            ,max(input(DATE,?? is8601da.)) as EDT format = yymmdd10.
                                _
                                22
                                200
 ERROR 22-322: 期望: 格式名.  
 
 ERROR 200-322: 该符号不可识别，将被忽略。

[ChrisNZ]

@Ksharp 谢谢 🙂