combination code

sg_kr — Tue, 27 Nov 2018 10:54:21 GMT

Hi,

i have a datasets with policy numbers with agent and their pc's like below

pol_no	agt_no	agt_name	PC
301	1	x	10
302	2	y	10
301	2	y	5
303	1	x	20
303	2	y	10

here there are few policies which are shared by both the agents like pol_no 301 and 303, but the pc's owned by the agents for same polices are different.

Now,i am trying to find the combinations based on their policy number and also whoever comes as agt_no1, his sharing(pc's) should assign to the pc column.

below is the code which i wrote but:

rsubmit;

title 'Find out max Joint Agent per Policy';

data polds2;
set polds;
by group PPGFirm pol_no;
retain &ValueVarList2.;
if first.pol_no then count=0;
count+1;
run;

*indicate #agents joint-sale*;
proc freq;tables count/list missing out=MaxAgtCount;run;
data _null_;
set MaxAgtCount;
Counter=count+0;
call symput ('MaxAgtCount',counter);
run;
%put &MaxAgtCount.;

title 'Add all the joint agent per each policy';
%macro JointDetail;

data polds3(keep=group PPGFirm pol_no Count &ValueVarList2. %do y=1 %to 7; agt_no&y. Agtname&y. %end;);
retain pol_no &ValueVarList2. %do y=1 %to 7; agt_no&y. Agtname&y. %end;;
set polds2;
by group PPGFirm pol_no;

length %do y=1 %to 7; agt_no&y. $7. Agtname&y. $70. %end;;
retain %do y=1 %to 7; agt_no&y. Agtname&y. %end;;

if first.pol_no then do;
%do y=1 %to 7;
agt_no&y.=''; Agtname&y.='';
%end;
end;
%do y=1 %to &MaxAgtCount.;
if Count=&y. then do;
agt_no&y.=agt_no; Agtname&y.=Agtname;
end;
%end;
run;

data polds3_1(keep=group PPGFirm pol_no Count &ValueVarList2. %do y=1 %to 7; agt_no&y. Agtname&y. %end;);
retain pol_no &ValueVarList2. %do y=1 %to 7; agt_no&y. Agtname&y. %end;;
set polds2;
by group PPGFirm pol_no;

length %do y=1 %to 7; agt_no&y. $7. Agtname&y. $70. %end;;
retain %do y=1 %to 7; agt_no&y. Agtname&y. %end;;

if last.pol_no then do;
%do y=1 %to 7;
agt_no&y.=''; Agtname&y.='';
%end;
end;
%do y=1 %to &MaxAgtCount.;
if Count=&y. then do;
agt_no&y.=agt_no; Agtname&y.=Agtname;
end;
%end;
run;

desired output:

pol_no	agt_no1	agt_name1	agt_no2	agt_name2	pc
301	1	x	2	y	10
301	2	y	1	x	5
302	2	y			10
303	1	x	2	y	20
303	2	y	1	x	10

please help me with the small modifications that i can do on the above code.

thanks in advance.

Re: combination code

PeterClemmensen — Tue, 27 Nov 2018 14:31:11 GMT

First of all, it is rarely a good idea to make the data set wide.

Secondly, how is pc=5 in the second row of your desired output calculated?

Re: combination code

sg_kr — Wed, 28 Nov 2018 10:28:07 GMT

bcoz, he owns those pc's for that pol_no

topic Re: combination code in SAS Programming

combination code

Re: combination code

Re: combination code