Trouble debugging code-How to resolve

[Jssa8423]

Hello everyone,

I'm getting this error message in my log. I tried a few things. Any suggestions?

(SUM(t1.DETERM_CASE)) AS SUM_of_DETERM_CASE,
52 t1.DETERM_CASE,
53 /* DAT_DIF */
54 (INTCK('YEAR',t1.LST_DCN_DT, t1.DOD)) LABEL="DAT_DIF" AS DAT_DIF,
55 /* DAT_DIF_1 */
56 (YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) LABEL="DAT_DIF_1" AS DAT_DIF_1
57 FROM ODA_FTRS.DASH_INIT_REPORT t1
58 WHERE t1.QDD_CASES = 1 AND YEAR(t1.ERLST_EFF_FLG_DT) =2014 AND t1.ALLOW_CASE = 1 AND t1.AGE_AT_DCN_DT < 18;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Invalid (or missing) arguments to the INTCK function have caused the function to return a missing value.
NOTE: Invalid argument to function YRDIF. Missing values may be generated.
NOTE: Table WORK.QUERY_FOR_DASH_INITIAL_CASE_REPO created, with 1 rows and 10 columns.

[PaigeMiller]

There are no ERRORs in the log that you show us.

NOTEs do not necessarily indicate problems that have to be "resolved". What resolution would you like?

[Jssa8423]

I would like to resolve

NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Invalid (or missing) arguments to the INTCK function have caused the function to return a missing value.
NOTE: Invalid argument to function YRDIF. Missing values may be generated.
NOTE: Table WORK.QUERY_FOR_DASH_INITIAL_CASE_REPO created, with 1 rows and 10 columns.

The program is not producing any results.

[PaigeMiller]

again, I ask ... what resolution would you like?

You are getting a data set that has one observation and 10 columns. This is not what you claimed that "The program is not producing any results". Are you expecting more observations? Are you expecting more columns? If so, can you please indicate what the desired output is?

We also need to see the ENTIRE log for this PROC SQL, not just the last dozen lines or so. Paste the log into the window that appears when you click on the </> icon, DO NOT SKIP THIS STEP.

[Jssa8423]

Here is the entire log

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Allowed Adult QDD Mortality Filed 2014';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='C:\Users\117415\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\7875I2WH\Gerry History
5 ! Shell.egp';
6 %LET _CLIENTPROJECTPATHHOST='S352052';
7 %LET _CLIENTPROJECTNAME='Gerry History Shell.egp';
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=PNG;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 %_eg_conditional_dropds(WORK.QUERY_FOR_DASH_INITIAL_CASE_REPO);
NOTE: Table WORK.QUERY_FOR_DASH_INITIAL_CASE_REPO has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

25
26 PROC SQL;
27 CREATE TABLE WORK.QUERY_FOR_DASH_INITIAL_CASE_REPO AS
28 SELECT /* DEATHCAT */
29 (case
30 when t1.DOD IS MISSING
31 then 'No Death Recorded'
32 when (YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) < 1 and t1.DOD NOT IS MISSING
33 then 'Less than 1 Year'
34 when ((YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) BETWEEN 1 and 1.99999999) and t1.DOD NOT IS MISSING
35 then '1 Years'
36 when ((YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) BETWEEN 2 and 2.99999999) and t1.DOD NOT IS MISSING
37 then '2 Years'
38 when ((YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) BETWEEN 3 and 3.99999999) and t1.DOD NOT IS MISSING
39 then '3 Years'
40 when ((YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) BETWEEN 4 and 4.99999999) and t1.DOD NOT IS MISSING
41 then '4 Years'
42 when ((YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) >= 5) and t1.DOD NOT IS MISSING
43 then '5 Years+'
44 end) AS DEATHCAT,
45 t1.FLDR_NUM,
46 t1.CASE_NUM,
47 t1.RECEIPT_CASES,
48 t1.ERLST_EFF_FLG_DT,
49 t1.DOD,
50 /* SUM_of_DETERM_CASE */
2 The SAS System 11:47 Tuesday, August 3, 2021

51 (SUM(t1.DETERM_CASE)) AS SUM_of_DETERM_CASE,
52 t1.DETERM_CASE,
53 /* DAT_DIF */
54 (INTCK('YEAR',t1.LST_DCN_DT, t1.DOD)) LABEL="DAT_DIF" AS DAT_DIF,
55 /* DAT_DIF_1 */
56 (YRDIF(t1.LST_DCN_DT, t1.DOD, 'ACT/ACT')) LABEL="DAT_DIF_1" AS DAT_DIF_1
57 FROM ODA_FTRS.DASH_INIT_REPORT t1
58 WHERE t1.QDD_CASES = 1 AND YEAR(t1.ERLST_EFF_FLG_DT) =2014 AND t1.ALLOW_CASE = 1 AND t1.AGE_AT_DCN_DT < 18;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Invalid (or missing) arguments to the INTCK function have caused the function to return a missing value.
NOTE: Invalid argument to function YRDIF. Missing values may be generated.
NOTE: Table WORK.QUERY_FOR_DASH_INITIAL_CASE_REPO created, with 1 rows and 10 columns.

59 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 1:57.22
cpu time 51.18 seconds

60
61 GOPTIONS NOACCESSIBLE;
62
63
64 %LET _CLIENTTASKLABEL=;
65 %LET _CLIENTPROCESSFLOWNAME=;
66 %LET _CLIENTPROJECTPATH=;
67 %LET _CLIENTPROJECTPATHHOST=;
68 %LET _CLIENTPROJECTNAME=;
69
70 ;*';*";*/;quit;run;
71 ODS _ALL_ CLOSE;
72
73
74 QUIT; RUN;
75

Yes, I was expected observations and not just missing observations. 

[PaigeMiller]

You have this line in your code:

WHERE t1.QDD_CASES = 1 AND YEAR(t1.ERLST_EFF_FLG_DT) =2014 AND t1.ALLOW_CASE = 1 AND t1.AGE_AT_DCN_DT < 18

If you are getting fewer observations than you expect, then the problem is in here somehow. One or more of these conditions is removing observations that you do not expect it to remove. You can remove these conditions one-at-a-time to see if these are improperly removing observations.

So for example, you can comment out part of this WHERE like this

WHERE t1.QDD_CASES = 1 AND YEAR(t1.ERLST_EFF_FLG_DT) =2014 AND t1.ALLOW_CASE = 1 /* AND t1.AGE_AT_DCN_DT < 18 */

and see if this particular change helps. Similarly, you can comment out each of the other parts of the WHERE to see if it is causing the wrong observations to be excluded.

[Kurt_Bremser]

NOTE: The query requires remerging summary statistics back with the original data.

This means that you have a column in the SELECT that is neither the argument of a summary function, nor part of the GROUP BY clause. Instead of just having one observation per group, the original number of observations is taken, and the result(s) of the summary function(s) are stored in each observation.

Fix your SELECT and GROUP BY, if you did not intend this.

NOTE: Invalid (or missing) arguments to the INTCK function have caused the function to return a missing value.

What it says. You have values in the columns used in the INTCK functions that can't be computed, so it returns missing values. Check your data.

NOTE: Invalid argument to function YRDIF. Missing values may be generated.

Same as above. Check your data.

NOTE: Invalid argument to function YRDIF. Missing values may be generated.

This is a NOTE you will always get; it simply notifies you of the basic outline of the resulting dataset.

It mostly boils down to Maxim 3: Know Your Data.

Inspect your dataset, especially paying attention to the formats used, so you get a clue about the raw values stored in there. It might be that you try to use a datetime where a date is expected.