Result generated are different from expectation.

[Wken1122]

Hi,

I have run the following codes fro days yet I could not get the result that i have been expecting.
The result that I had been expecting are more than 1 rows and yet it only stay as 1 rows despite the status of other rows are not in any of the above.
Please provide me with insights.
Thank you.
**Attached is the sas file that I had been running.

[Reeza]

Post your program as text into the forum directly please.

[Wken1122]

%INCLUDE "/folders/myfolders/Folder1/sasmacro/array.sas";
%INCLUDE "/folders/myfolders/Folder1/sasmacro/do_over.sas";

%LET LIB_SOURCE=CH_P02_SOURCE_5;
%LET LIB_ODSCLAIM=DM_BASE.ODS_CLAIM;

DATA ODS_CLAIM;
SET ODS_CLAIM;
RUN;

PROC SQL;
CREATE TABLE CLAIMS_DETAILS AS
(
SELECT ID AS CLAIMID,ODS_CLAIM.POLICYNUMBER,PRODUCTC,CLAIMNUMBER,CLAIMNOTIFIEDDATE,STATUS,OCCURENCEDATE,LOSSTYPE,
TOTALPAIDAMOUNT,INTIMATEDDATE,DATEADMITTED,DATEDISCHARGED,
DATEAPPROVED,LOSSDESCRIPTION,TRANSACTIONNUMBER,APPROVEDAMOUNT,
LOSSDESCRIPTIONCODE,CLAIMENTRYDATE,CAUSEOFEVENTCODE,CLOSEDATE,LOSSDATE,ILLNESSCODE,ILLNESS

FROM ODS_CLAIM, (SELECT DISTINCT (POLICYNUMBER) FROM CH_P02_SOURCE_5) TEMP
Where ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER
and STATUS NOT IN ('Cancelled','Decline','Rejected'
,'Pending Assessment','Pending Authorization','Pending Documents')
)
;RUN; QUIT;

[Reeza]

Can you post a PROC FREQ output from the STATUS column? And the FULL log.

And does the following change the results at all?

PROC SQL ;
	CREATE TABLE CLAIMS_DETAILS AS
   SELECT ID AS CLAIMID, ODS_CLAIM.POLICYNUMBER, 
	PRODUCTC, CLAIMNUMBER,CLAIMNOTIFIEDDATE, STATUS, OCCURENCEDATE, LOSSTYPE, TOTALPAIDAMOUNT, 
		INTIMATEDDATE, DATEADMITTED, DATEDISCHARGED, DATEAPPROVED, LOSSDESCRIPTION, 
		TRANSACTIONNUMBER, APPROVEDAMOUNT, LOSSDESCRIPTIONCODE, CLAIMENTRYDATE, 
		CAUSEOFEVENTCODE, CLOSEDATE, LOSSDATE, ILLNESSCODE, ILLNESS 
		
		FROM ODS_CLAIM full join 
		(SELECT DISTINCT (POLICYNUMBER) FROM CH_P02_SOURCE_5) as TEMP 
		on 
		ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER 
		where STATUS NOT IN ('Cancelled', 'Decline', 'Rejected', 'Pending Assessment', 'Pending Authorization', 'Pending Documents') ;

QUIT;

[Wken1122]

Hi,

The full join does not make any changes.
Attached is the requested files.

[Reeza]

There's errors in your log. Fix the steps with the errors first - you truncated the log so I can't say where. 

EDIT - the full log is there, didn't show in preview for some reason. Please do not use attachement but post things directly into the forum. 

It's easier to work with. The answers the same still though, your SQL query has a syntax error, I think you need the word AS for the alias. 

Fix that, and you'll likely at least be one step further.  If there's an error in your log it helps if you state that at the start and include it, rather than us having to ask for it. And always read the log. 

[Wken1122]

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
NOTE: ODS statements in the SAS Studio environment may disable some output features.
62
63 %INCLUDE "/folders/myfolders/Folder1/sasmacro/array.sas";
219 %INCLUDE "/folders/myfolders/Folder1/sasmacro/do_over.sas";
459
460 %LET LIB_SOURCE=CH_P02_SOURCE_5;
461 %LET LIB_ODSCLAIM=DM_BASE.ODS_CLAIM;
462
463 DATA ODS_CLAIM;
464 SET ODS_CLAIM;
465 RUN;

NOTE: There were 220 observations read from the data set WORK.ODS_CLAIM.
NOTE: The data set WORK.ODS_CLAIM has 220 observations and 107 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


466
467 PROC SQL;
468 CREATE TABLE CLAIMS_DETAILS AS
469 (
470 SELECT ID AS CLAIMID,ODS_CLAIM.POLICYNUMBER,PRODUCTC,CLAIMNUMBER,CLAIMNOTIFIEDDATE,STATUS,OCCURENCEDATE,LOSSTYPE,
471 TOTALPAIDAMOUNT,INTIMATEDDATE,DATEADMITTED,DATEDISCHARGED,
472 DATEAPPROVED,LOSSDESCRIPTION,TRANSACTIONNUMBER,APPROVEDAMOUNT,
473 LOSSDESCRIPTIONCODE,CLAIMENTRYDATE,CAUSEOFEVENTCODE,CLOSEDATE,LOSSDATE,ILLNESSCODE,ILLNESS
474
475 FROM ODS_CLAIM, (SELECT DISTINCT(POLICYNUMBER) FROM &LIB_SOURCE.) TEMP
476 WHERE ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER
477 AND STATUS NOT IN ('Cancelled','Decline','Rejected'
478 ,'Pending Assessment','Pending Authorization','Pending Documents')
479 )
480 ;
NOTE: Table WORK.CLAIMS_DETAILS created, with 1 rows and 23 columns.

480 ! RUN; QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds


481
482 DATA CLAIMS_DETAILS;
483 SET CLAIMS_DETAILS;
484 FORMAT LAST1M LAST3M LAST6M LAST12M LAST18M LAST24M LAST60M LAST120M DATE9.;
485 LAST1M=DATEPART(DATEADMITTED)-30;
486 LAST3M=DATEPART(DATEADMITTED)-90;
487 LAST6M=DATEPART(DATEADMITTED)-180;
488 LAST12M=DATEPART(DATEADMITTED)-(12*30);
489 LAST18M=DATEPART(DATEADMITTED)-(18*30);
490 LAST24M=DATEPART(DATEADMITTED)-(24*30); /*2YEARS*/
491 LAST60M=DATEPART(DATEADMITTED)-(60*30); /*5YEARS*/
492 LAST120M=DATEPART(DATEADMITTED)-(120*30); /*10YEARS*/
493 CLAIM_HOSP_LOS= DATEPART(DATEDISCHARGED)-DATEPART(DATEADMITTED);
494 RUN;

NOTE: There were 1 observations read from the data set WORK.CLAIMS_DETAILS.
NOTE: The data set WORK.CLAIMS_DETAILS has 1 observations and 32 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


495
496 /*
497 PROC FREQ DATA=CLAIMS_DETAILS NOPRINT;
498 TABLE STATUS/OUT=STATUS;
499 RUN;
500 */
501
502 PROC SORT DATA=CLAIMS_DETAILS; BY POLICYNUMBER DATEADMITTED; RUN;

NOTE: There were 1 observations read from the data set WORK.CLAIMS_DETAILS.
NOTE: The data set WORK.CLAIMS_DETAILS has 1 observations and 32 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


503
504 DATA CLAIMS_DETAILS_ALL(DROP=LAG_TOTALPAIDAMOUNT);
505 SET CLAIMS_DETAILS;
506 BY POLICYNUMBER;
507 RETAIN CLAIMCNT_ALLPAST CLAIMAMT_ALLPAST;
508 LAG_TOTALPAIDAMOUNT=LAG(TOTALPAIDAMOUNT);
509 IF FIRST.POLICYNUMBER THEN
510 DO;
511 CLAIMCNT_ALLPAST=0;
512 CLAIMAMT_ALLPAST=0;
513 LAG_TOTALPAIDAMOUNT=0;
514 END;
515 ELSE
516 DO;
517 CLAIMCNT_ALLPAST=CLAIMCNT_ALLPAST+1;
518 CLAIMAMT_ALLPAST=CLAIMAMT_ALLPAST+LAG_TOTALPAIDAMOUNT;
519 END;
520 RUN;

NOTE: There were 1 observations read from the data set WORK.CLAIMS_DETAILS.
NOTE: The data set WORK.CLAIMS_DETAILS_ALL has 1 observations and 34 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds


521
522 %MACRO DOCLAIMSUM(MTH);
523 PROC SQL;
524 CREATE TABLE CLAIMS_DETAILS_&MTH.M AS
525 (
526 SELECT LTAB.POLICYNUMBER,LTAB.CLAIMID, LTAB.DATEADMITTED
527 , COUNT(DISTINCT RTAB.DATEADMITTED) AS CLAIMCNT_LAST&MTH.M, SUM(RTAB.TOTALPAIDAMOUNT) AS CLAIMAMT_LAST&MTH.M
528 FROM CLAIMS_DETAILS_ALL LTAB
529 LEFT JOIN CLAIMS_DETAILS_ALL RTAB
530 ON LTAB.POLICYNUMBER=RTAB.POLICYNUMBER AND LTAB.LAST&MTH.M<=RTAB.DATEADMITTED< LTAB.DATEADMITTED /*if the prevous
530 ! admitted date is in betwehh x month from now*/
531 GROUP BY LTAB.POLICYNUMBER,LTAB.CLAIMID,LTAB.DATEADMITTED
532 ); RUN; QUIT;
533 %MEND;
534
535 %DO_OVER(VALUES=1 3 6 12 18 24 60 120, MACRO=DOCLAIMSUM); RUN; QUIT;
NOTE: Table WORK.CLAIMS_DETAILS_1M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds


NOTE: Table WORK.CLAIMS_DETAILS_3M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


NOTE: Table WORK.CLAIMS_DETAILS_6M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


NOTE: Table WORK.CLAIMS_DETAILS_12M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds


NOTE: Table WORK.CLAIMS_DETAILS_18M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds


NOTE: Table WORK.CLAIMS_DETAILS_24M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds


NOTE: Table WORK.CLAIMS_DETAILS_60M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


NOTE: Table WORK.CLAIMS_DETAILS_120M created, with 1 rows and 5 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


536
537
538 PROC SQL;
539
540 CREATE TABLE CLAIMS_DETAILS_FINAL AS
541 (
542 SELECT TAB.*,CLAIMCNT_ALLPAST,CLAIMAMT_ALLPAST
543 ,CLAIMCNT_LAST1M,CLAIMAMT_LAST1M
544 ,CLAIMCNT_LAST3M,CLAIMAMT_LAST3M
545 ,CLAIMCNT_LAST6M,CLAIMAMT_LAST6M
546 ,CLAIMCNT_LAST12M,CLAIMAMT_LAST12M
547 ,CLAIMCNT_LAST18M,CLAIMAMT_LAST18M
548 ,CLAIMCNT_LAST24M,CLAIMAMT_LAST24M
549 ,CLAIMCNT_LAST60M,CLAIMAMT_LAST60M
550 ,CLAIMCNT_LAST120M,CLAIMAMT_LAST120M
551 FROM CLAIMS_DETAILS TAB
552 LEFT JOIN CLAIMS_DETAILS_ALL TABSUM
553 ON TAB.POLICYNUMBER=TABSUM.POLICYNUMBER
554 AND TAB.CLAIMID=TABSUM.CLAIMID
555 AND TAB.DATEADMITTED=TABSUM.DATEADMITTED
556 LEFT JOIN CLAIMS_DETAILS_1M TAB1M
557 ON TAB.POLICYNUMBER=TAB1M.POLICYNUMBER
558 AND TAB.CLAIMID=TAB1M.CLAIMID
559 AND TAB.DATEADMITTED=TAB1M.DATEADMITTED
560 LEFT JOIN CLAIMS_DETAILS_3M TAB3M
561 ON TAB.POLICYNUMBER=TAB3M.POLICYNUMBER
562 AND TAB.CLAIMID=TAB3M.CLAIMID
563 AND TAB.DATEADMITTED=TAB3M.DATEADMITTED
564 LEFT JOIN CLAIMS_DETAILS_6M TAB6M
565 ON TAB.POLICYNUMBER=TAB6M.POLICYNUMBER
566 AND TAB.CLAIMID=TAB6M.CLAIMID
567 AND TAB.DATEADMITTED=TAB6M.DATEADMITTED
568 LEFT JOIN CLAIMS_DETAILS_12M TAB12M
569 ON TAB.POLICYNUMBER=TAB12M.POLICYNUMBER
570 AND TAB.CLAIMID=TAB12M.CLAIMID
571 AND TAB.DATEADMITTED=TAB12M.DATEADMITTED
572 LEFT JOIN CLAIMS_DETAILS_18M TAB18M
573 ON TAB.POLICYNUMBER=TAB18M.POLICYNUMBER
574 AND TAB.CLAIMID=TAB18M.CLAIMID
575 AND TAB.DATEADMITTED=TAB18M.DATEADMITTED
576 LEFT JOIN CLAIMS_DETAILS_24M TAB24M
577 ON TAB.POLICYNUMBER=TAB24M.POLICYNUMBER
578 AND TAB.CLAIMID=TAB24M.CLAIMID
579 AND TAB.DATEADMITTED=TAB24M.DATEADMITTED
580 LEFT JOIN CLAIMS_DETAILS_60M TAB60M
581 ON TAB.POLICYNUMBER=TAB60M.POLICYNUMBER
582 AND TAB.CLAIMID=TAB60M.CLAIMID
583 AND TAB.DATEADMITTED=TAB60M.DATEADMITTED
584 LEFT JOIN CLAIMS_DETAILS_120M TAB120M
585 ON TAB.POLICYNUMBER=TAB120M.POLICYNUMBER
586 AND TAB.CLAIMID=TAB120M.CLAIMID
587 AND TAB.DATEADMITTED=TAB120M.DATEADMITTED
588 )
589
590 ;
NOTE: Table WORK.CLAIMS_DETAILS_FINAL created, with 1 rows and 50 columns.

591 RUN;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds


592
593 /*ALSO INCLUDE THE LATEST ONE*/
594 DATA CLAIMS_DETAILS;
595 SET CLAIMS_DETAILS_FINAL;
596 %DO_OVER(VALUES=1 3 6 12 18 24 60 120,
597 PHRASE=IF MISSING(CLAIMAMT_LAST?M) THEN CLAIMAMT_LAST?M=0;
598 CLAIMCNT_LAST?M=CLAIMCNT_LAST?M+1;
599 CLAIMAMT_LAST?M=CLAIMAMT_LAST?M+TOTALPAIDAMOUNT;);
600 CLAIMCNT_ALLPAST=CLAIMCNT_ALLPAST+1;
601 CLAIMAMT_ALLPAST=CLAIMAMT_ALLPAST+TOTALPAIDAMOUNT;
602 RUN;

NOTE: There were 1 observations read from the data set WORK.CLAIMS_DETAILS_FINAL.
NOTE: The data set WORK.CLAIMS_DETAILS has 1 observations and 50 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


603
604 PROC DELETE DATA=ODS_CLAIM CLAIMS_DETAILS_FINAL CLAIMS_DETAILS_ALL CLAIMS_DETAILS_1M CLAIMS_DETAILS_3M CLAIMS_DETAILS_6M
605 CLAIMS_DETAILS_12M CLAIMS_DETAILS_18M CLAIMS_DETAILS_24M CLAIMS_DETAILS_60M CLAIMS_DETAILS_120M; RUN;

NOTE: Deleting WORK.ODS_CLAIM (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_FINAL (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_ALL (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_1M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_3M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_6M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_12M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_18M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_24M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_60M (memtype=DATA).
NOTE: Deleting WORK.CLAIMS_DETAILS_120M (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds


606
607
608
609 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
622

[Reeza]

Sadly as someone else has mentioned we can't help without the data now. 

Your code works, now, but the logic is wrong, since we don't know the logic you want, or the data you have and the code runs....

I would start with a couple of things - your first data step does nothing. Is that correct/expected?

Check the variable length for policy number, is it the same in both data sets? Are they character or numeric? If character does it have letter on both cases? 

Remove the status filter in the query and see what the results are? 

[Wken1122]

Ok then, I will give it a try...
Thanks a lot for all your quick reply.

[Shmuel]

It is impossible to check your code without having the input (test) data and the macro source code.

I suggest:

1) check the log and locate the first step output with one row. Is it logic ?

    Some intermediate output may have one row. Isn't it ?

    Beyond:

1) Remark last step of PROC DELETE and rerun in order to have the intermediate datasets in work.

2) Check step by step output to locate first step suspicious output and recheck the code logic.

3) When you find a suspicious step, add some PUT statements and add info to the log ang debug it.
    Recheck the log. If need change your step code and rerun that step until you feel it is ok.

4) Do the same recechecking on every suspicios step, up to the last.

    Finally rerun the full updated code and check results.

5) Unmark the PROC DELETE step and save the code.

Does this help you?

[Wken1122]

I will give it a try. Thank you.

[Shmuel]

Checking your log:

First step resulted in 220 observations. The next step result with 1 observation only.

Is that fit your expectations?

Focus on the log lines numbered 467 - 480. 

Check again your data - ODS_CLAIM dataset: 

Try to run with part of the selection conditions:

WHERE ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER

(without checking STATUS). What statuses you have in the output? how many fit your second condition?

We may fail with logic or with the code.

Check your logic accrding to code and vice versa.

[Wken1122]

On second step there should have more than 1 row fits the criteria but unsure why it only shows a single row.

[Reeza]

I genuinely suspect your first data step is logically incorrect. I suspect you should be sourcing the data from a library? Perhaps the one assigned in the macro variable. 

[Shmuel]

Run next code and post your output as text:

%LET lib_source =  ...;  /* copmlete this statement ! */

PROC SQL;
    CREATE TABLE CLAIMS_DETAILS AS
    ( SELECT  ID AS CLAIMID,
                     ODS_CLAIM.POLICYNUMBER,
                     PRODUCTC,
                     CLAIMNUMBER,
                     CLAIMNOTIFIEDDATE,
                     STATUS,
                     OCCURENCEDATE,
                     LOSSTYPE,
                     TOTALPAIDAMOUNT,
                     INTIMATEDDATE,
                     DATEADMITTED,
                     DATEDISCHARGED,
                     DATEAPPROVED,
                     LOSSDESCRIPTION,
                     TRANSACTIONNUMBER,
                     APPROVEDAMOUNT,
                     LOSSDESCRIPTIONCODE,
                     CLAIMENTRYDATE,
                     CAUSEOFEVENTCODE,
                     CLOSEDATE,
                     LOSSDATE,
                     ILLNESSCODE,
                     ILLNESS

FROM ODS_CLAIM,
         (SELECT DISTINCT(POLICYNUMBER) FROM &LIB_SOURCE.) 
          as TEMP
WHERE ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER
/*******
AND STATUS NOT IN ('Cancelled','Decline','Rejected'
					,'Pending Assessment','Pending Authorization','Pending Documents')
*******/
       );
QUIT;

PROC PRINT DATA=CLAIMS_DETAILS;
   var  STATUS ;
run;