BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
piyushdwij
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
DartRodrigo
Lapis Lazuli | Level 10

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

4 REPLIES 4
DartRodrigo
Lapis Lazuli | Level 10

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

 

LinusH
Tourmaline | Level 20

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
piyushdwij
Calcite | Level 5

thanks 🙂

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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