PROC SQL update counts

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

PROC SQL update counts

Hi SAS users,

 

how to get the # of records which got  updated in the table (dbms.oracle_table), In log i can see # of records updated , but i want to capture those #'s for my furthur analysis in SAS program. Is there any easier way?

 

PROC SQL;
update dbms.oracle_table A
SET
test_date = (SELECT B.test_date FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
),
.test_upload = (SELECT B.test_upload FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
),
A.test_reason = (SELECT B.test_reason FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
)
where exists (
select 1
from MANUAL_TEMP B
where
A.temp1 = B.temp1
AND A.temp2 = B.temp2
;

QUIT;


Accepted Solutions
Solution
‎10-29-2016 07:34 PM
Respected Advisor
Posts: 4,606

Re: PROC SQL update counts

How about the automatic macro variable SQLOBS? Look at

 

proc sql;

create table class as select * from sashelp.class;

update class
set age = 99
where age=12;

quit;

%put &sqlobs;
PG

View solution in original post


All Replies
Solution
‎10-29-2016 07:34 PM
Respected Advisor
Posts: 4,606

Re: PROC SQL update counts

How about the automatic macro variable SQLOBS? Look at

 

proc sql;

create table class as select * from sashelp.class;

update class
set age = 99
where age=12;

quit;

%put &sqlobs;
PG
Frequent Contributor
Posts: 81

Re: PROC SQL update counts

Thank you . I need to read those observations in creating another SQL table to capture the updated counts.
Respected Advisor
Posts: 4,606

Re: PROC SQL update counts

You could capture the update counts this way:

 

proc sql;
/* Create table to capture update counts */
create table updates (when date format=datetime19., howMany numeric);

/* Do some update operation */
create table class as select * from sashelp.class;
update class
set age = 99
where age=12;

/* Add time and update count to the update counts table */
insert into updates set when=datetime(), howMany=&sqlobs;

select * from updates;
quit;
PG
Grand Advisor
Posts: 9,576

Re: PROC SQL update counts

1) you can use PROC PRINTTO to redirect the LOG into a log file, and you can read that number from it by writing some SAS code.

 

2) Or just select the record as your SQL did ?

 

select count(*) from  dbms.oracle_table

where exists (
select 1
from MANUAL_TEMP B
where
A.temp1 = B.temp1
AND A.temp2 = B.temp2
;

QUIT;

Frequent Contributor
Posts: 81

Re: PROC SQL update counts

Thanks much, i created a dummy table and used insert statement and added counts & other details there.

Thanks,
Ana
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 320 views
  • 1 like
  • 3 in conversation