Proc sql insert

[buckeyefisher]

I want to update a record by reading another record in the same file. Example given below. Both the records are in the same file. Field to be updated is partner experience based on the value of Partner ID.

I looked up at various proc sql insert examples but was not sure.

before

#          Company ID             Partner ID         self experience  partner experience

1          A                             B                      5                     -  

2          B                             A                     10                    - 

after running proc SQL - it should look like this,

#          Company ID             Partner ID         self experience  partner experience

1          A                             B                      5                     10  

2          B                             A                     10                   5

[ballardw]

Do you have any cases where there are more than 2 of the company and partner id combinations? If so does more than one have values for partner experience?

[buckeyefisher]

Ye, there could be more than one partners. Partner ID 1 , Partner ID 2. (up to 8 partners)

[buckeyefisher]

Also when there are many partners, the field partner experience should be sum of all partners' production experience.

[ballardw]

You should update your example to show the multiple partner id and expected results.

[PGStats · Accepted solution]

It is UPDATE that you need, not INSERT :

data have;

input  CompanyID $ PartnerID $ selfExperience partnerExperience;

datalines;

A                             B                      5                     . 

B                             A                     10                    .

;

proc sql;

create table self as

select unique companyID, selfExperience

from have;

update have as H

set partnerExperience = (

    select selfExperience

    from self

    where companyID=H.partnerID);

drop table self;

select * from have;

quit;

PG