topic Re: proc sql insert doesnt work in SAS Enterprise Guide

proc sql insert doesnt work

l0calh0st — Tue, 13 Apr 2021 10:59:40 GMT

Hello!

I am using SAS EG 8.3, and now I am having troubles with inserting values into table in external server.

proc sql;
insert into rsk_cbs.FOR_FINAL_SPR_REP
select * from FOR_FINAL_SPR_REP_WORK;
run;

It says that

NOTE: 130 rows were inserted into RSK_CBS.FOR_FINAL_SPR_REP.

but it didn't. why could this happen?

Re: proc sql insert doesnt work

Kurt_Bremser — Tue, 13 Apr 2021 11:06:48 GMT

Please run this code:

proc sql;
select nobs from dictionary.tables
where libname = "RSK_CBS" and memname = "FOR_FINAL_SPR_REP";
quit;

proc sql;
insert into rsk_cbs.FOR_FINAL_SPR_REP
select * from FOR_FINAL_SPR_REP_WORK;
run;

proc sql;
select nobs from dictionary.tables
where libname = "RSK_CBS" and memname = "FOR_FINAL_SPR_REP";
quit;

and post both the log and the output.

Re: proc sql insert doesnt work

l0calh0st — Tue, 13 Apr 2021 11:19:43 GMT

output:

why is it empty?

Re: proc sql insert doesnt work

l0calh0st — Tue, 13 Apr 2021 11:20:57 GMT

log output:

1 The SAS System 16:22 Tuesday, April 13, 2021

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Программа 1';
4 %LET _CLIENTPROCESSFLOWNAME='Схема процесса';
5 %LET _CLIENTPROJECTPATH='C:\Users\sayabaea\Desktop\active\моделирование\скоринг по авто\auto_strategy_DT_210413_edw.egp';
6 %LET _CLIENTPROJECTPATHHOST='PC000000028524';
7 %LET _CLIENTPROJECTNAME='auto_strategy_DT_210413_edw.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 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HtmlBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 proc sql;
29 select nobs from dictionary.tables
30 where libname = "RSK_CBS" and memname = "FOR_FINAL_SPR_REP";
NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 5 table(s) have not been displayed/returned.
31 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.45 seconds
cpu time 0.01 seconds

32
33 proc sql;
34 insert into rsk_cbs.FOR_FINAL_SPR_REP
35 select * from FOR_FINAL_SPR_REP_WORK;
NOTE: 130 rows were inserted into RSK_CBS.FOR_FINAL_SPR_REP.

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

38 proc sql;
39 select nobs from dictionary.tables
40 where libname = "RSK_CBS" and memname = "FOR_FINAL_SPR_REP";
NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 5 table(s) have not been displayed/returned.
41 quit;
NOTE: PROCEDURE SQL used (Total process time):
2 The SAS System 16:22 Tuesday, April 13, 2021

real time 0.15 seconds
cpu time 0.01 seconds

42
43 %LET _CLIENTTASKLABEL=;
44 %LET _CLIENTPROCESSFLOWNAME=;
45 %LET _CLIENTPROJECTPATH=;
46 %LET _CLIENTPROJECTPATHHOST=;
47 %LET _CLIENTPROJECTNAME=;
48 %LET _SASPROGRAMFILE=;
49 %LET _SASPROGRAMFILEHOST=;
50
51 ;*';*";*/;quit;run;
52 ODS _ALL_ CLOSE;
53
54
55 QUIT; RUN;
56

Re: proc sql insert doesnt work

Kurt_Bremser — Tue, 13 Apr 2021 11:38:19 GMT

So you have table names in your DBMS that do not conform to rules for SAS names; this may cause that you insert your observations into a another table than the one you look at.

How did you verify that the obs were not inserted in the first place?

Re: proc sql insert doesnt work

l0calh0st — Tue, 13 Apr 2021 12:03:54 GMT

I inserted data for specific day, and those observations for that day are empty

Re: proc sql insert doesnt work

Kurt_Bremser — Tue, 13 Apr 2021 12:17:33 GMT

@l0calh0st wrote:
I inserted data for specific day, and those observations for that day are empty

So it DID insert observations, but with missing values (apart from the date)?