Trying to find the remaining number from table 1

[Sandeep77]

Hi Experts,

I have created a table which finds the dialled number from confirm_email table. Now I want to find which numbers were left out (not dialled) from the confirm_email table. Here is my code below. Can you please suggest what can I add to find the numbers that were not dialled?

proc sql;
create table Dialled_numbers as
select distinct
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
;
quit;

[PeterClemmensen]

Needless to say, this is untested, but something like this will do

proc sql;
create table Dialled_numbers as
select distinct
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
b.dcPhoneNumber
from work.confirmed_email
where account_number not in (select dcaccountnumber from DIALFILE.tDiallerCalls)
;
quit;

[Sandeep77]

Thank you @PeterClemmensen  I tried running the code as you suggested and still showing error. Please suggest how to correct it. 

proc sql;
create table Dialled_numbers as
select distinct
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
b.dcPhoneNumber
from work.confirmed_email as a
where account_number not in (select dcaccountnumber from DIALFILE.tDiallerCalls)
;
quit;

error log:

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         b.dcPhoneNumber /*is the number that was dialled */
33         
34         from work.confirmed_email as a
35         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
36         ;
NOTE: Compressing data set WORK.DIALLED_NUMBERS increased size by 39.29 percent. 
      Compressed is 117 pages; un-compressed would require 84 pages.
NOTE: Table WORK.DIALLED_NUMBERS created, with 302745 rows and 1 columns.

37         create table Not_Dialled_numbers as
38         select distinct
39         b.dcPhoneNumber
40         from work.confirmed_email as a
41         where account_number not in (select dcaccountnumber from DIALFILE.tDiallerCalls)
42         ;
ERROR: Unresolved reference to table/correlation name b.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           11:43.41
      user cpu time       11:47.64
      system cpu time     1:32.53
      memory              6305153.67k
2                                                          The SAS System                            16:21 Tuesday, October 11, 2022

      OS Memory           6329244.00k
      Timestamp           10/11/2022 05:11:05 PM
      Step Count                        20  Switch Count  83
      
44         
45         %LET _CLIENTTASKLABEL=;
46         %LET _CLIENTPROCESSFLOWNAME=;
47         %LET _CLIENTPROJECTPATH=;
48         %LET _CLIENTPROJECTPATHHOST=;
49         %LET _CLIENTPROJECTNAME=;
50         %LET _SASPROGRAMFILE=;
51         %LET _SASPROGRAMFILEHOST=;
52         
53         ;*';*";*/;quit;run;
54         ODS _ALL_ CLOSE;
55         
56         
57         QUIT; RUN;
58         

[Patrick]

Just try to understand why the error message is telling you what's it's telling you and then it should be pretty simple to fix the code.