Help using Base SAS procedures

Proc sql insert

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

Proc sql insert

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


Accepted Solutions
Solution
‎05-28-2014 05:18 PM
Respected Advisor
Posts: 4,644

Re: Proc sql insert

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


All Replies
Super User
Posts: 10,490

Re: Proc sql insert

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?

 

Contributor
Posts: 63

Re: Proc sql insert

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

Contributor
Posts: 63

Re: Proc sql insert

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

Super User
Posts: 10,490

Re: Proc sql insert

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

Solution
‎05-28-2014 05:18 PM
Respected Advisor
Posts: 4,644

Re: Proc sql insert

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
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 264 views
  • 0 likes
  • 3 in conversation