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
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
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?
Ye, there could be more than one partners. Partner ID 1 , Partner ID 2. (up to 8 partners)
Also when there are many partners, the field partner experience should be sum of all partners' production experience.
You should update your example to show the multiple partner id and expected results.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.