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

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Obsidian | Level 7

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

buckeyefisher
Obsidian | Level 7

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

ballardw
Super User

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

PGStats
Opal | Level 21

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

PG

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!

What is Bayesian Analysis?

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.

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
  • 5 replies
  • 968 views
  • 0 likes
  • 3 in conversation