topic Data set merge in SAS Programming

Data set merge

Paul_NYS — Fri, 14 Nov 2014 12:15:03 GMT

I am trying to merge two data sets (something I have done 500 times). The field I am trying to add to the first from the second is not copying correctly in that the values are not matching correctly based on the 'by' variable. I have tried it several different ways and nothing seems to work. Anyone see anything? Its the AppearCount variable that I want to add and the values for it are not matching. Also the second data set (appearances2bt1) is actually a subset of the first.

Paul

proc sort data=appearances2b;
by docket_id;
run;

proc sort data=appearances2bt1;
by docket_id;
run;

data AppearanceAttendDen;
length EventType $ 50;
merge appearances2b (in=a) appearances2bt1 (keep=docket_id AppearCount);
if a;
by docket_id;

if event in ("AAD","AHR","ACT","AIQ","ANE","ANH","ACD","AHY","ASJ")
then EventType="Adjudication";

if event in ("DDD","DRR","DPP","DTP","DAJ","DSP","DDS","DOR",
"DWD") then EventType="Disposition";

if event in ("XCA","XRF","XRL","XGA","XAP","XRM","XOT") then EventType="Exit";

if event in ("PHR","PHC") then EventType="PermanencyHearing";

drop AGEOUT IY NYC FCOUNT OCOUNT SCOUNT TCOUNT PCOUNT HCOUNT
DURCAT_ADJUD_DISPO1 DURCAT_ADJUD1 DUR_PL1 DURCAT_PL1
PLACE_CAT1 TADJUD_FLAG CENSOR_TADJUD1 CENSOR_TADJUD1 DURCAT_TADJUD_TDISPO1 DURCAT_TADJUD_TDISPO1
durmod durmod_cat state link ageout count fcount_notpr place_end1 help durmoa durmoa_cat exit_pl1 stop_pl1
place1y;

run;

Re: Data set merge

Kurt_Bremser — Fri, 14 Nov 2014 12:53:49 GMT

Are you sure that you do not have an AppearCount in appearances2b already?

Re: Data set merge

advoss — Fri, 14 Nov 2014 13:55:41 GMT

Yes, KurtBremser has made the correct observation.

Tell us what you are really trying to do, because the two tables are equivalent. They have the same number of rows, and both have the variables docket_id and AppearCount with the exact same values...unless you are doing some additional processing on appearances2bt1 that you have not shown.

Assuming the latter, just drop AppearCount from appearances2b and you should get what you are expecting.

Re: Data set merge

Rakeon — Fri, 14 Nov 2014 20:18:03 GMT

Hi,

I met your same problem with the merge statement,

In this post we talk about the merge statement in SAS: