topic Proc Sql Updates from 2 tables in SAS Programming

Proc Sql Updates from 2 tables

Jyuen204 — Tue, 19 Nov 2019 22:16:52 GMT

Stupid question but I cant seen to get to work.

I have a table with names and IDs (AGENT_REPORT)

I have another table with ID's (AGENT_TRANSFERS)

Trying to pull over the names to my table with ID's only

proc sql;
update AGENT_TRANSFERS as A
set AGENT_NAME = (select AGENT_NAME from AGENT_REPORT as B
where A.SRC_AGNT_LAN_ID = B.SRC_AGNT_LAN_ID);
run;

I get this error

_
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

Apologies, I'm used to coding in Toad and utilizing that syntax but trying to adapt some of this to SAS. Thank you

Re: Proc Sql Updates from 2 tables

PGStats — Tue, 19 Nov 2019 22:37:55 GMT

Looks OK to me, except for the run; statement (it's quit; with SQL)

Please post a more complete log using the {i} button.

Re: Proc Sql Updates from 2 tables

Ksharp — Wed, 20 Nov 2019 11:53:41 GMT

Try feedback option , if you are connecting DB try these options.

options sastrace=',,,d' sastraceloc=saslog;
proc sql feedback;
...........
quit;

Re: Proc Sql Updates from 2 tables

Jyuen204 — Wed, 20 Nov 2019 15:52:17 GMT

Tried a few options

proc sql;
update AGENT_TRANSFERS as A
set AGENT_NAME = (select AGENT_NAME from AGENT_REPORT as B
where A.SRC_AGNT_LAN_ID = B.SRC_AGNT_LAN_ID);
quit;

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROJECTPATH='L:\Data_Analytics\Customer Data Insights\Deliverables\Recurring\Jason\BPI - Blueprints\Nordia
4 ! Package Report V6.egp';
5 %LET _CLIENTPROJECTNAME='Nordia Package Report V6.egp';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/6.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;

23 proc sql;
24 update AGENT_TRANSFERS as A
25 set A.AGENT_NAME = (select B.AGENT_NAME from AGENT_TMP as B
_
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

26 where A.SRC_AGNT_LAN_ID = B.AGENT_LAN_ID);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
27 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 34.06k
OS Memory 22176.00k
Timestamp 11/20/2019 10:48:37 AM
Step Count 233 Switch Count 50
Page Faults 0
Page Reclaims 7
Page Swaps 0
Voluntary Context Switches 146
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

28
29 GOPTIONS NOACCESSIBLE;
2 The SAS System 10:12 Wednesday, November 20, 2019

30 %LET _CLIENTTASKLABEL=;
31 %LET _CLIENTPROJECTPATH=;
32 %LET _CLIENTPROJECTNAME=;
33 %LET _SASPROGRAMFILE=;
34
35 ;*';*";*/;quit;run;
36 ODS _ALL_ CLOSE;
37
38
39 QUIT; RUN;

Re: Proc Sql Updates from 2 tables

Jyuen204 — Wed, 20 Nov 2019 15:55:01 GMT

no DB connections, just trying to bring in a value from another table

Re: Proc Sql Updates from 2 tables

Jyuen204 — Wed, 20 Nov 2019 17:47:41 GMT

Ah i figured it out, one of the tables was using an alias and my brain was too tired to realize it.....