The output data is not showing results

[Sandeep77]

Hi Experts,

I have written a code to find the dialled numbers and the numbers which are not dialled. The dialled numbers shows all the results but the Not dialled number code runs but does not generate any data in the output data. Can you please check and let me know the reason? Thanks

proc sql;
create table Dialled_numbers as
select distinct
a.account_number,
b.dcPhoneNumber /*is the number that was dialled */

from work.confirmed_email as a
inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
;
create table Not_Dialled_numbers as
select distinct
a.account_number,
b.dcPhoneNumber
from work.confirmed_email as a
inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
where a.account_number not in (select a.account_number from Dialled_numbers)
;
quit;

Log:

29         /*
30         proc sql;
31         create table Dialled_numbers as
32         select distinct
33         b.dcPhoneNumber (is the number that was dialled )
34         
35         from work.confirmed_email as a
36         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
37         ;
38         create table Not_Dialled_numbers as
39         select distinct
40         b.dcPhoneNumber
41         from work.confirmed_email
42         where account_number not in (select dcaccountnumber from DIALFILE.tDiallerCalls)
43         ;
44         quit;*/
45         
46         proc sql;
47         create table Dialled_numbers as
48         select distinct
49         a.account_number,
50         b.dcPhoneNumber /*is the number that was dialled */
51         
52         from work.confirmed_email as a
53         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
54         ;
NOTE: Compressing data set WORK.DIALLED_NUMBERS decreased size by 14.29 percent. 
      Compressed is 204 pages; un-compressed would require 238 pages.
NOTE: Table WORK.DIALLED_NUMBERS created, with 370049 rows and 2 columns.
2                                                          The SAS System                             08:55 Monday, October 17, 2022


55         create table Not_Dialled_numbers as
56         select distinct
57         a.account_number,
58         b.dcPhoneNumber
59         from work.confirmed_email as a
60         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
61         where a.account_number not in (select a.account_number from Dialled_numbers)
62         ;
NOTE: Table WORK.NOT_DIALLED_NUMBERS created, with 0 rows and 2 columns.

63         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           13:05.82
      user cpu time       12:07.28
      system cpu time     1:35.43
      memory              6305300.60k
      OS Memory           6328988.00k
      Timestamp           10/17/2022 09:37:20 AM
      Step Count                        19  Switch Count  88
      

[Kurt_Bremser]

Your mistake is here:

where a.account_number not in (select a.account_number from Dialled_numbers)

You select  the number from your "a" dataset for every obs in dialled_numbers, instead of the number stored there.

It should be

where a.account_number not in (select account_number from Dialled_numbers)

[Sandeep77]

Thank you. I tried doing it before but it did not worked. As you suggested, I did it again and it does not show any results. Please check the log and can you suggest the reason for this?

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Dialled numbers';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='S:\Trace\Trace Integrity Analyst Data\Projects and Tasks\3. Sandeep\Dialler\Dialler V1.egp';
6          %LET _CLIENTPROJECTPATHHOST='LWLT5CG9322XFL';
7          %LET _CLIENTPROJECTNAME='Dialler V1.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         ODS LISTING GPATH=&sasworklocation;
18         FILENAME EGHTML TEMP;
19         ODS HTML5(ID=EGHTML) FILE=EGHTML
20             OPTIONS(BITMAP_MODE='INLINE')
21             %HTML5AccessibleGraphSupported
22             ENCODING='utf-8'
23             STYLE=HTMLBlue
24             NOGTITLE
25             NOGFOOTNOTE
26             GPATH=&sasworklocation
27         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         proc sql;
30         create table Dialled_numbers as
31         select distinct
32         a.account_number,
33         b.dcPhoneNumber /*is the number that was dialled */
34         
35         from work.confirmed_email as a
36         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
37         ;
NOTE: Compressing data set WORK.DIALLED_NUMBERS decreased size by 14.29 percent. 
      Compressed is 204 pages; un-compressed would require 238 pages.
NOTE: Table WORK.DIALLED_NUMBERS created, with 370049 rows and 2 columns.

38         create table Not_Dialled_numbers as
39         select distinct
40         a.account_number,
41         b.dcPhoneNumber
42         from work.confirmed_email as a
43         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
44         where a.account_number not in (select account_number from Dialled_numbers)
45         ;
NOTE: Table WORK.NOT_DIALLED_NUMBERS created, with 0 rows and 2 columns.

46         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           22:23.88
      user cpu time       19:56.20
      system cpu time     2:32.46
      memory              6305243.03k
2                                                          The SAS System                             08:55 Monday, October 17, 2022

      OS Memory           6330012.00k
      Timestamp           10/17/2022 10:27:47 AM
      Step Count                        21  Switch Count  88
      

47         
48         %LET _CLIENTTASKLABEL=;
49         %LET _CLIENTPROCESSFLOWNAME=;
50         %LET _CLIENTPROJECTPATH=;
51         %LET _CLIENTPROJECTPATHHOST=;
52         %LET _CLIENTPROJECTNAME=;
53         %LET _SASPROGRAMFILE=;
54         %LET _SASPROGRAMFILEHOST=;
55         
56         ;*';*";*/;quit;run;
57         ODS _ALL_ CLOSE;
58         
59         
60         QUIT; RUN;
61         

[Kurt_Bremser · Accepted solution]

Your dialled_numbers dataset is created with the same join you use in the second SELECT, which means it will contain all account numbers appearing there, so you filter everything out. Try a FULL JOIN instead of the INNER JOIN.

[Sandeep77]

Thank you 🙂

[Kurt_Bremser]

By providing usable example data (data steps with datalines) to give us an idea of your data, we could provide more efficient code to solve your issue.