topic Re: proc sql update statement in SAS Programming

proc sql update statement

piyushdwij — Mon, 19 Oct 2015 11:30:16 GMT

I have two tables, Table A has user Id and 5 different product columns(empty, to be filled by count ). Table B has timestamp user id and product id ( purchased at time timestamp ). This code id giving error

Proc SQL;

update table_A as table_A

set Count_Product_1 =

(select count(product_ID) from Table_B inner join Table_A on Table_A.User_ID=Table_B.User_ID

where Product_ID='Unique_identifier_product_1');

Quit;

error: You cannot reopen Table_A for update access with member-level control because Table_A is in use by you in resource environment SQL

Re: proc sql update statement

DartRodrigo — Mon, 19 Oct 2015 11:43:57 GMT

Hi, morning.

You can't update a table with itself. You need to create a copy of the table as done in below code.

The technique Geraldo proposes would also work as long as the string of account numbers doesn't exceed 32K (the max. storage capacity of a SAS macro variable).

In your case with Account_Num of length 10 plus 2 quotes and a comma per account number you could maximally store 2520 distinct values.

data EMB2;
  infile datalines dsd dlm=',';
  input Account_Num:$10. As_of_Dt:date9. Account_Status $; 
  format As_of_Dt date9.;
  retain New_Downgrade_Flag ' ';
datalines;
S77856455E,31Jan2011,Passed
TX2342331F,31Jan2011,Passed
S77856455E,28Feb2011,Passed
TX2342331F,28Feb2011,Loss
;
run;
PROC SQL;
  create table EMB2_int as
    SELECT ACCOUNT_NUM FROM EMB2
    WHERE ACCOUNT_STATUS IN ('Passed', 'Special Mention (Tech)', 'Special Mention')
          AND AS_OF_DT = '31JAN2011'D
  ;
  UPDATE EMB2 AS U
    SET NEW_DOWNGRADE_FLAG = '1' 
    where 
          U.ACCOUNT_STATUS IN ('Doubtful', 'Sub-Standard', 'Loss')
      AND U.AS_OF_DT = '28FEB2011'D
      AND U.ACCOUNT_NUM in (select I.ACCOUNT_NUM from EMB2_int I)
  ;
  drop table EMB2_int
  ;
quit;
proc print data=EMB2;
run;

Check these links : PROC SQL - UPDATE

PROC SQL UPDATE PROBLEM

This is another question about the same metter.

Hope this might help you Mate

Re: proc sql update statement

LinusH — Mon, 19 Oct 2015 11:50:39 GMT

By using de-normalised table structure, you are making life harder. What happens when you decide to count 7 products, and so forth? Wide table format should only be used in report output and data mining analytic tables.

Transpose the target table design ("A") by using a combined key with ID and Product. Then you should be able to calculate this in one execution. Or if you like, still by product, but just using append/insert into syntax.

Re: proc sql update statement

RW9 — Mon, 19 Oct 2015 11:55:53 GMT

Hi,

Just to add my two penneth, yes this isn't a good idea. There are options, however you are better doing this in datastep, or creating a new dataset, i.e. do a join.

Re: proc sql update statement

piyushdwij — Mon, 19 Oct 2015 12:09:04 GMT

thanks 🙂