Hi
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;
Are you sure that you do not have an AppearCount in appearances2b already?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.