topic Re: help in my sql code in SAS Programming

help in my sql code

knveraraju91 — Mon, 02 Mar 2020 16:53:29 GMT

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;

Re: help in my sql code

tomrvincent — Mon, 02 Mar 2020 17:00:13 GMT

3rd record has no date.

Re: help in my sql code

tomrvincent — Mon, 02 Mar 2020 17:02:42 GMT

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.

Re: help in my sql code

mbuchecker — Mon, 02 Mar 2020 17:07:34 GMT

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

Re: help in my sql code

ChrisNZ — Mon, 02 Mar 2020 19:48:02 GMT

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.

Re: help in my sql code

Ksharp — Tue, 03 Mar 2020 03:43:00 GMT

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

Re: help in my sql code

ChrisNZ — Tue, 03 Mar 2020 03:55:34 GMT

> 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.

Re: help in my sql code

Ksharp — Tue, 03 Mar 2020 04:42:09 GMT

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: 该符号不可识别，将被忽略。

Re: help in my sql code

ChrisNZ — Tue, 03 Mar 2020 20:40:33 GMT

@Ksharp 谢谢 🙂