Trying to find the remaining number from table 1

Sandeep77 — Tue, 11 Oct 2022 13:00:49 GMT

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;

Re: Trying to find the remaining number from table 1

PeterClemmensen — Tue, 11 Oct 2022 13:22:58 GMT

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;

Re: Trying to find the remaining number from table 1

Sandeep77 — Tue, 11 Oct 2022 16:14:03 GMT

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

Re: Trying to find the remaining number from table 1

Patrick — Wed, 12 Oct 2022 02:05:59 GMT

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.

topic Trying to find the remaining number from table 1 in New SAS User

Trying to find the remaining number from table 1

Re: Trying to find the remaining number from table 1

Re: Trying to find the remaining number from table 1

Re: Trying to find the remaining number from table 1