topic Re: how to get a number how many row was inserted ? in SAS Data Management

how to get a number of rows that were inserted ?

Dawid3 — Tue, 26 Apr 2022 19:20:25 GMT

Hi everyone, I wrote some code for sas DI where I get the start and end time of the job.
Does anyone know how to get a number, how many row was inserted?

%global startTm endTm;

%macro startTiming();

%let startTm = %sysfunc(datetime());

%mend startTiming;

%macro endTiming(entry=);

%let endTm = %sysfunc(datetime());

proc sql;
insert into lib.Tab
values("&Entry", &startTm, &endTm );
quit;

%mend;

%startTiming();

CODE OF JOB

%endTiming(entry=Tab);

Re: how to get a number how many row was inserted ?

LinusH — Tue, 26 Apr 2022 12:14:29 GMT

If you have access to SAS Environment Manager, I think this information would be available if you activate performance logging:

https://documentation.sas.com/doc/en/etlug/4.903/p1xs37ux2hnk80n1h9bdj5gnswcn.htm

DI Studio has the macto variable etls_recnt avaible, but that only shows the no of records in the target table after the update, not no of records added/updated. I guess that you could have a pre-code to get the no of records before the the loader, and then calculate at the end no of records inserted?

Re: how to get a number of rows that were inserted ?

Dawid3 — Tue, 26 Apr 2022 19:19:54 GMT

Unfortunately I don"t have access to SAS Environment Manager.
I think I could get a pre-code to get the no. of records before the loader and then calculate at the end no. of records inserted - as you say
Can you tell me how to get it?
I would like to enter the data that I have into the table where I have the start and end time.

Re: how to get a number how many row was inserted ?

PaigeMiller — Tue, 26 Apr 2022 12:54:45 GMT

I haven't tried this, but if you add

%put &sqlobs;

after your PROC SQL, it should display the number of observations (rows).

Re: how to get a number how many row was inserted ?

Dawid3 — Wed, 04 May 2022 05:45:50 GMT

I use this code in pre-code to get the no. of records before the loader and then calculate at the end no. of records inserted, it works.

data _NULL_;
if 0 then set sashelp.class nobs=n;
call symputx('nrows',n);
stop;
run;
%put nobs=&nrows;