set year of date based bon proc sql

[France]

dear all.

could you please give me suggestions about how to restrict the year of 'earliest_publn_date' not equal to 9999?

when I use following codes,

PROC SQL;
  CREATE TABLE No_of_citation AS
   SELECT
   t_base.psn_name,
   COUNT(DISTINCT t_do.docdb_family_id)
   FROM base_of_citation AS t_base
   JOIN Sample.Applications AS t_ap1 ON t_ap1.docdb_family_id=t_base.docdb_family_id_base  
   JOIN Sample.Docdbfamiliescitations AS t_do ON t_do.cited_docdb_family_id=t_base.docdb_family_id_base
   JOIN Sample.Applications AS t_ap2 ON t_ap2.docdb_family_id=t_do.docdb_family_id
   JOIN Sample.Publications AS t_pu ON t_pu.appln_id=t_ap2.appln_id
  WHERE
   YEAR(t_ap1.earliest_publn_date) != 9999
   AND YEAR(t_pu.publn_date) != 9999
   AND t_pu.publn_date <= intnx('year',t_ap1.earliest_publn_date,6)                                   
  ;
QUIT;

I get the result like this,

329  PROC SQL;
330    CREATE TABLE No_of_citation AS
331
332     SELECT
333
334     t_base.psn_name,
335
336     COUNT(DISTINCT t_do.docdb_family_id)
337
338     FROM base_of_citation AS t_base
339
340     JOIN Sample.Applications AS t_ap1 ON t_ap1.docdb_family_id=t_base.docdb_family_id_base
341
342          /* in order to set the moving window */
343
344     JOIN Sample.Docdbfamiliescitations AS t_do ON
344! t_do.cited_docdb_family_id=t_base.docdb_family_id_base
345
346     JOIN Sample.Applications AS t_ap2 ON t_ap2.docdb_family_id=t_do.docdb_family_id
347
348     JOIN Sample.Publications AS t_pu ON t_pu.appln_id=t_ap2.appln_id
349
350    WHERE
351
352     YEAR(t_ap1.earliest_publn_date) !=9999
                                         -
                                         22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT,
              SUBSTRING, USER.

353
354     AND YEAR(t_pu.publn_date) !=9999
354     AND YEAR(t_pu.publn_date) !=9999
                                   -
                                   22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT,
              SUBSTRING, USER.

355
356     AND t_pu.publn_date <= intnx('year',t_ap1.earliest_publn_date,6)
357
358                                      /* a 6-year moving window starting at the earliest
358! publication date of a patent famliy */
359
360     ORDER BY t_base.psn_name
361
362    ;
363  QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

the information about publn_date looks like following,

"Column Name", "Type", "Length", "Format", "Informat", "Label", "Transcode",

"publn_date", "Number", "8", "YYMMDDD10.", "", "", "No",

thanks in advance.

[ballardw · Accepted solution]

Try NE instead of the != . NE is short for "not equal", there are also GT, LT, GE and LE for "greater than", "less than", "greater than or equal" and "less than or equal".

Creating fictitious date values, assuming you have them with year 9999 for a "not actual value" is really not needed. Just set the dates as missing and then use:  not missing(t_pu.publn_date).