Hi mkeintz,
Just an additional help is needed.
I just want to compare all the Depo_IDs for one group to Shop_IDs, if all the IDs matched then Comments should print "Matched" or else "Not Matched".
Pet_Names
Depo_Count
Depo_IDs
Shop_Count
Shop_IDs
Comments
Depo_Id_Not_In_Shop
Shop_Id_Not_in_Depo
Dog
2
6346560,6354972
1
6354972
Matched
6346560
Dog
1
6360302
2
6346560,6360302
Matched
6346560
Cat
1
6320478,6296089
1
6296088
Not Matched
6320478,6296089
6296088
Cat
3
6296088,6327084,6354370
3
6320478,6327084,6354370
Not Matched
6296088
6320478
Pigeon
1
6320478,6296089
0
.
Not Matched
6320478,6296089
6296088
Pigeon
3
6296088,6327084,6354370
3
6320478,6327084,6354370
Not Matched
6296088
6320478
Parrot
1
6327084
1
6327084
Matched
The above is the expected output.
Like, From Dog Category total 3 Depo_Ids are there and that has to get compared with 3 Shop_Ids from Dog Category. You can see if 3 vs 3 will be compared, token by token then it will be matched and Comments Column should go as "Matched".
Same like for Cat: 4 vs 4 will be compared
Pigeon: 4 vs 3 will be compared and so on.
In simple words, all the Depo_ids from one Pet_names group will be compared to Shop_ids from the same group, if matched then Comments=Matched else Not matched.
I have already done the same with almost 5 steps.
The below is my code:
FILENAME REFFILE 'C:\Pet_Depo.xlsx';
PROC IMPORT DATAFILE=REFFILE replace
DBMS=XLSX
OUT=WORK.Pet_Depo;
GETNAMES=YES;
RUN;
proc sort data=Pet_Depo;
by Pet_Names;
run;
data grp_id_plt(drop=Comments);
set Pet_Depo ;
length Depo_Id_concat Shop_Id_concat $400;
by Pet_Names ;
retain Depo_Id_concat Shop_Id_concat ;
if first.Pet_Names then do ;
group_id =1;
Depo_Id_concat ='';
Shop_Id_concat ='';
end;
do i=1 to countw(Depo_IDs);
Depo_Id_concat=catx(';',strip(Depo_Id_concat),scan(Depo_IDs,i,','));
end;
do i=1 to countw(Shop_IDs);
Shop_Id_concat =catx(";",strip(Shop_Id_concat),scan(Shop_IDs,i,','));
end;
group_id+1;
if last.Pet_Names then output;
run;
data Comments_holder;
length Comments $20;
set grp_id_plt;
set_con_shop = 'Matched';
do i=1 to countw(Shop_Id_concat,';');
if set_con_shop = 'Matched' then do;
con_shop = findw(Depo_Id_concat,strip(scan(Shop_Id_concat,i))) ;
if con_shop = 0 then set_con_shop = 'Not Matched';
end;
end;
set_con_depo = 'Matched';
do i=1 to countw(Depo_Id_concat,';');
if set_con_depo = 'Matched' then do;
con_depo = findw(Shop_Id_concat ,strip(scan(Depo_Id_concat,i))) ;
if con_depo = 0 then set_con_depo = 'Not Matched';
end;
end;
if set_con_shop = 'Matched' and set_con_depo ='Matched' then Comments = 'Matched';
else Comments = 'Not Matched';
drop set_con_shop set_con_depo con_shop con_depo i ;
run;
PROC SQL;
CREATE TABLE WORK.Final AS
SELECT DISTINCT t1.Pet_Names,
t1.Depo_Count,
t1.Depo_IDs,
t1.Shop_Count,
t1.Shop_IDs,
t1.Depo_Id_Not_In_Shop,
t1.Shop_Id_Not_in_Depo,
t2.Comments
FROM WORK.Pet_Depo t1
LEFT JOIN WORK.Comments_HOLDER t2 ON (t1.Pet_Names = t2.Pet_Names);
QUIT;
Please suggest if any alternate way can I do the same or in lesser steps if it's possible suppose.
Thanks
... View more
