topic Re: Proc sql insert in SAS Procedures

Proc sql insert

buckeyefisher — Wed, 28 May 2014 18:55:46 GMT

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

Re: Proc sql insert

ballardw — Wed, 28 May 2014 19:32:51 GMT

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?

Re: Proc sql insert

buckeyefisher — Wed, 28 May 2014 20:17:17 GMT

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

Re: Proc sql insert

buckeyefisher — Wed, 28 May 2014 21:12:12 GMT

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

Re: Proc sql insert

ballardw — Wed, 28 May 2014 21:16:14 GMT

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

Re: Proc sql insert

PGStats — Wed, 28 May 2014 21:18:34 GMT

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;