DATA Step, Macro, Functions and more

proc sql update statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

proc sql update statement

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


Accepted Solutions
Solution
‎10-19-2015 08:08 AM
Regular Contributor
Posts: 212

Re: proc sql update statement

[ Edited ]

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 Smiley Happy

 

View solution in original post


All Replies
Solution
‎10-19-2015 08:08 AM
Regular Contributor
Posts: 212

Re: proc sql update statement

[ Edited ]

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 Smiley Happy

 

Super User
Posts: 5,260

Re: proc sql update statement

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.

 

Data never sleeps
Occasional Contributor
Posts: 6

Re: proc sql update statement

thanks Smiley Happy

Super User
Super User
Posts: 7,413

Re: proc sql update statement

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 454 views
  • 0 likes
  • 4 in conversation