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.
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).
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.