Re: merging by numeric variables

mona4u · Posted 07-01-2020 01:50 PM

I have two data sets, I'm trying to merge and I have difficulty with it.

data all;
infile datalines delimiter=',';
input ALAT $ CELL_TYPE $ XXGRPID : $ 13-36 PARAMCD $ n1 sum1;
datalines ;
RIGHT,B cell,Element 4 / Cluster 01,MIP3A,1,1.052443
RIGHT,B cell,Element 4 / Cluster 01,MCP2,1,1.32448
LEFT,B cell,Element 4 / Cluster 01,IL18,1,4.755031
RIGHT,B cell,Element 4 / Cluster 01,IL1B,2,3.298
RIGHT,B cell,Element 4 / Cluster 01,IL23,2,4.08961
RIGHT,B cell,Element 4 / Cluster 01,IL18,2,5.116604
RIGHT,B cell,Element 4 / Cluster 01,MDC,2,6.731015
RIGHT,B cell,Element 4 / Cluster 01,MMP9,2,8.833029
LEFT,B cell,Element 4 / Cluster 06,IL10,1,1.080867
RIGHT,B cell,Element 4 / Cluster 06,TNFA,1,1.125977
RIGHT,B cell,Element 4 / Cluster 06,IL17A,1,1.389087
LEFT,B cell,Element 4 / Cluster 06,IL1B,1,1.424445
LEFT,B cell,Element 4 / Cluster 06,IL17A,1,1.426559
LEFT,B cell,Element 4 / Cluster 06,IL8,1,6.045133
RIGHT,B cell,Element 4 / Cluster 06,IL8,1,24.14631
RIGHT,B cell,Element 4 / Cluster 06,MIP3A,2,3.106238
LEFT,B cell,Element 4 / Cluster 06,MCP2,2,6.049441
LEFT,B cell,Element 4 / Cluster 06,IL23,2,9.139846
RIGHT,B cell,Element 4 / Cluster 06,IL6,3,4.573806
RIGHT,B cell,Element 4 / Cluster 06,IL10,3,4.80698
RIGHT,B cell,Element 4 / Cluster 06,MCP2,3,30.499324
RIGHT,B cell,Element 4 / Cluster 06,IL12P35,4,17.608009
LEFT,B cell,Element 4 / Cluster 06,MMP9,4,29.593297
LEFT,B cell,Element 4 / Cluster 06,IL18,4,87.835631
RIGHT,B cell,Element 4 / Cluster 06,IL23,4,115.177666
RIGHT,B cell,Element 4 / Cluster 06,IL1B,4,120.746378
RIGHT,B cell,Element 4 / Cluster 06,IL18,4,397.20805
RIGHT,B cell,Element 4 / Cluster 06,MMP9,4,513.050819
RIGHT,B cell,Element 4 / Cluster 06,MDC,4,869.979232
LEFT,B cell,Element 4 / Cluster 06,MDC,5,74.628744
LEFT,B cell,Element 4 / Cluster 08,IL6,1,1.092431
RIGHT,B cell,Element 4 / Cluster 08,IL8,1,1.112689
LEFT,B cell,Element 4 / Cluster 08,TNFA,1,1.199949
RIGHT,B cell,Element 4 / Cluster 08,TNFA,1,1.415118
LEFT,B cell,Element 4 / Cluster 08,IL17A,1,3.476767
LEFT,B cell,Element 4 / Cluster 08,IL10,2,2.823709
RIGHT,B cell,Element 4 / Cluster 08,IL6,2,7.815049
RIGHT,B cell,Element 4 / Cluster 08,IL17A,3,3.274684
LEFT,B cell,Element 4 / Cluster 08,IL1B,3,17.548967
LEFT,B cell,Element 4 / Cluster 08,IL23,3,41.185114
LEFT,B cell,Element 4 / Cluster 08,MCP2,4,14.063476
RIGHT,B cell,Element 4 / Cluster 08,IL10,4,21.271532
RIGHT,B cell,Element 4 / Cluster 08,MCP2,4,36.627036
RIGHT,B cell,Element 4 / Cluster 08,IL1B,5,53.167223
RIGHT,B cell,Element 4 / Cluster 08,IL23,5,134.282479
LEFT,B cell,Element 4 / Cluster 08,MMP9,5,151.561209
LEFT,B cell,Element 4 / Cluster 08,IL18,5,210.739236
RIGHT,B cell,Element 4 / Cluster 08,IL18,5,213.896347
RIGHT,B cell,Element 4 / Cluster 08,MMP9,5,362.274311
LEFT,B cell,Element 4 / Cluster 08,MDC,5,1127.607899
RIGHT,B cell,Element 4 / Cluster 08,MDC,5,1375.540752
RIGHT,B cell,Element 4 / Cluster 10,IL10,1,1.462359
RIGHT,B cell,Element 4 / Cluster 10,IL1B,1,2.832492
RIGHT,B cell,Element 4 / Cluster 10,MMP9,1,6.815063
RIGHT,B cell,Element 4 / Cluster 10,IL18,1,8.054599
RIGHT,B cell,Element 4 / Cluster 10,MDC,1,8.580185
RIGHT,B cell,Element 4 / Cluster 10,IL23,1,9.352442
RIGHT,B cell,Element 4 / Cluster 11,MIP3A,1,1.027387
RIGHT,B cell,Element 4 / Cluster 11,IL6,1,1.111179
RIGHT,B cell,Element 4 / Cluster 11,IL23,1,1.209556
RIGHT,B cell,Element 4 / Cluster 11,IL1B,1,1.330708
RIGHT,B cell,Element 4 / Cluster 11,IL17A,1,1.479449
LEFT,B cell,Element 4 / Cluster 11,IL12P35,1,1.865239
RIGHT,B cell,Element 4 / Cluster 11,IL10,1,3.810738
LEFT,B cell,Element 4 / Cluster 11,MIP3A,1,5.648318
RIGHT,B cell,Element 4 / Cluster 11,IL12P35,2,5.071999
LEFT,B cell,Element 4 / Cluster 11,MCP2,2,7.793822
RIGHT,B cell,Element 4 / Cluster 11,MMP9,2,13.794082
RIGHT,B cell,Element 4 / Cluster 11,MDC,2,19.827618
RIGHT,B cell,Element 4 / Cluster 11,IL18,2,23.457758
LEFT,B cell,Element 4 / Cluster 11,IL23,3,9.420101
LEFT,B cell,Element 4 / Cluster 11,IL1B,3,15.071652
LEFT,B cell,Element 4 / Cluster 11,MMP9,3,40.127112
LEFT,B cell,Element 4 / Cluster 11,MDC,3,80.743343
LEFT,B cell,Element 4 / Cluster 11,IL18,3,102.263439
RIGHT,B cell,Element 4 / Cluster 12,IL6,1,1.092244
LEFT,B cell,Element 4 / Cluster 12,IL23,1,1.145162
LEFT,B cell,Element 4 / Cluster 12,MCP2,1,3.248554
LEFT,B cell,Element 4 / Cluster 12,IL8,1,3.871407
RIGHT,B cell,Element 4 / Cluster 12,IL23,2,2.330385
RIGHT,B cell,Element 4 / Cluster 12,IL8,2,4.22409
LEFT,B cell,Element 4 / Cluster 12,IL1B,2,14.739847
RIGHT,B cell,Element 4 / Cluster 12,MMP9,3,3.615062
LEFT,B cell,Element 4 / Cluster 12,MMP9,3,6.240014
RIGHT,B cell,Element 4 / Cluster 12,IL18,3,13.520781
LEFT,B cell,Element 4 / Cluster 12,IL18,4,12.77423
RIGHT,B cell,Element 4 / Cluster 12,IL1B,4,14.212594
RIGHT,B cell,Element 4 / Cluster 12,MDC,4,23.995677
LEFT,B cell,Element 4 / Cluster 12,MDC,4,34.696228
LEFT,B cell,Element 4 / Cluster 13,IL8,1,1.128982
RIGHT,B cell,Element 4 / Cluster 13,TNFA,1,1.44388
LEFT,B cell,Element 4 / Cluster 13,IL6,1,2.082078
LEFT,B cell,Element 4 / Cluster 13,IL17A,1,4.402744
LEFT,B cell,Element 4 / Cluster 13,IL10,2,7.772832
RIGHT,B cell,Element 4 / Cluster 13,IL17A,3,5.103111
RIGHT,B cell,Element 4 / Cluster 13,IL6,3,5.812486
LEFT,B cell,Element 4 / Cluster 13,MCP2,3,20.320418
RIGHT,B cell,Element 4 / Cluster 13,IL10,4,14.683562
RIGHT,B cell,Element 4 / Cluster 13,IL1B,4,31.831035
LEFT,B cell,Element 4 / Cluster 13,IL1B,4,37.352226
RIGHT,B cell,Element 4 / Cluster 13,IL23,4,41.013705
LEFT,B cell,Element 4 / Cluster 13,IL23,4,43.419341
RIGHT,B cell,Element 4 / Cluster 13,MCP2,5,20.093032
LEFT,B cell,Element 4 / Cluster 13,MMP9,5,91.797495
RIGHT,B cell,Element 4 / Cluster 13,IL18,5,135.063839
RIGHT,B cell,Element 4 / Cluster 13,MMP9,5,164.904232
LEFT,B cell,Element 4 / Cluster 13,IL18,5,204.537065
LEFT,B cell,Element 4 / Cluster 13,MDC,5,963.484488
RIGHT,B cell,Element 4 / Cluster 13,MDC,5,1352.556329
RIGHT,B cell,Element 4 / Cluster 14,MCP2,1,1.394026
LEFT,B cell,Element 4 / Cluster 14,IL1B,1,1.436841
LEFT,B cell,Element 4 / Cluster 14,MMP9,1,2.799828
RIGHT,B cell,Element 4 / Cluster 14,IL23,1,3.091322
LEFT,B cell,Element 4 / Cluster 14,MDC,1,4.155055
LEFT,B cell,Element 4 / Cluster 14,IL23,1,4.427726
LEFT,B cell,Element 4 / Cluster 14,IL18,1,4.688802
RIGHT,B cell,Element 4 / Cluster 14,IL18,1,6.105653
RIGHT,B cell,Element 4 / Cluster 14,MMP9,1,8.197537
RIGHT,B cell,Element 4 / Cluster 14,MDC,1,9.795185
RIGHT,B cell,Element 4 / Cluster 15,MMP9,1,1.072943
RIGHT,B cell,Element 4 / Cluster 15,IL18,2,2.900656
LEFT,B cell,Element 4 / Cluster 15,MDC,3,8.709494
RIGHT,B cell,Element 4 / Cluster 15,MDC,4,33.672651
LEFT,B cell,Element 4 / Cluster 16,TNFA,1,1.195228
RIGHT,B cell,Element 4 / Cluster 16,TNFA,1,1.292952
RIGHT,B cell,Element 4 / Cluster 16,MIP3A,1,2.846047
LEFT,B cell,Element 4 / Cluster 16,MIP3A,1,3.520069
RIGHT,B cell,Element 4 / Cluster 16,MDC,1,4.620938
LEFT,B cell,Element 4 / Cluster 16,IL18,1,9.352088
RIGHT,B cell,Element 4 / Cluster 16,IL18,2,4.376269
RIGHT,B cell,Element 4 / Cluster 16,IL23,2,7.823603
LEFT,B cell,Element 4 / Cluster 16,MDC,2,7.954463
LEFT,B cell,Element 4 / Cluster 16,IL23,2,11.08295
LEFT,B cell,Element 4 / Cluster 17,IL17A,1,1.307045
LEFT,B cell,Element 4 / Cluster 17,MIP3A,1,1.674991
LEFT,B cell,Element 4 / Cluster 17,IL8,1,1.695314
RIGHT,B cell,Element 4 / Cluster 17,IL8,1,2.280916
RIGHT,B cell,Element 4 / Cluster 17,IL6,1,2.367407
LEFT,B cell,Element 4 / Cluster 17,TNFA,2,2.312621
LEFT,B cell,Element 4 / Cluster 17,MCP2,2,4.437333
LEFT,B cell,Element 4 / Cluster 17,IL1B,2,7.251691
RIGHT,B cell,Element 4 / Cluster 17,IL23,2,9.972712
RIGHT,B cell,Element 4 / Cluster 17,MCP2,3,5.721486
RIGHT,B cell,Element 4 / Cluster 17,IL1B,3,12.466984
RIGHT,B cell,Element 4 / Cluster 17,IL10,3,16.6982
LEFT,B cell,Element 4 / Cluster 17,IL23,3,16.838101
LEFT,B cell,Element 4 / Cluster 17,MMP9,5,40.266312
RIGHT,B cell,Element 4 / Cluster 17,MMP9,5,59.809416
RIGHT,B cell,Element 4 / Cluster 17,IL18,5,69.125958
LEFT,B cell,Element 4 / Cluster 17,IL18,5,175.913002
LEFT,B cell,Element 4 / Cluster 17,MDC,5,454.373929
RIGHT,B cell,Element 4 / Cluster 17,MDC,5,484.506671
RIGHT,B cell,Element 4 / Cluster 18,IL17A,1,1.033533
LEFT,B cell,Element 4 / Cluster 18,IL12P35,1,1.033765
RIGHT,B cell,Element 4 / Cluster 18,TNFA,1,1.03978
LEFT,B cell,Element 4 / Cluster 18,IL10,1,1.084055
LEFT,B cell,Element 4 / Cluster 18,IL23,1,1.312616
RIGHT,B cell,Element 4 / Cluster 18,IL8,1,3.166954
RIGHT,B cell,Element 4 / Cluster 18,IL1B,2,2.288673
LEFT,B cell,Element 4 / Cluster 18,MCP2,2,3.402
RIGHT,B cell,Element 4 / Cluster 18,MCP2,2,3.702223
RIGHT,B cell,Element 4 / Cluster 18,IL10,3,4.570338
RIGHT,B cell,Element 4 / Cluster 18,IL23,3,5.178791
RIGHT,B cell,Element 4 / Cluster 18,IL6,3,5.487968
LEFT,B cell,Element 4 / Cluster 18,MMP9,3,9.448696
LEFT,B cell,Element 4 / Cluster 18,IL18,4,28.503497
RIGHT,B cell,Element 4 / Cluster 18,MMP9,4,33.396653
RIGHT,B cell,Element 4 / Cluster 18,IL18,5,19.346905
LEFT,B cell,Element 4 / Cluster 18,MDC,5,215.503145
RIGHT,B cell,Element 4 / Cluster 18,MDC,5,354.41396
RIGHT,B cell,Element 4 / Cluster 19,IL23,1,1.022038
RIGHT,B cell,Element 4 / Cluster 19,IL6,1,1.471786
LEFT,B cell,Element 4 / Cluster 19,MMP9,1,1.91916
LEFT,B cell,Element 4 / Cluster 19,MDC,1,3.656766
RIGHT,B cell,Element 4 / Cluster 19,IL10,1,4.236916
LEFT,B cell,Element 4 / Cluster 19,IL18,1,4.585735
RIGHT,B cell,Element 4 / Cluster 19,MDC,2,9.187698
LEFT,B cell,Element 4 / Cluster 23,IL6,1,1.014265
RIGHT,B cell,Element 4 / Cluster 23,IL6,1,1.232044
LEFT,B cell,Element 4 / Cluster 23,IL10,1,1.351696
LEFT,B cell,Element 4 / Cluster 23,IL12P35,1,1.42176
RIGHT,B cell,Element 4 / Cluster 23,MIP3A,1,2.321669
RIGHT,B cell,Element 4 / Cluster 23,IL10,1,2.446623
LEFT,B cell,Element 4 / Cluster 23,IL17A,1,2.459378
RIGHT,B cell,Element 4 / Cluster 23,IL8,1,4.71809
LEFT,B cell,Element 4 / Cluster 23,MCP2,1,7.646246
RIGHT,B cell,Element 4 / Cluster 23,IL1B,2,6.401135
RIGHT,B cell,Element 4 / Cluster 23,MCP2,3,6.227043
LEFT,B cell,Element 4 / Cluster 23,IL1B,3,19.425768
LEFT,B cell,Element 4 / Cluster 23,IL23,3,47.362551
RIGHT,B cell,Element 4 / Cluster 23,IL23,4,21.779931
RIGHT,B cell,Element 4 / Cluster 23,MMP9,4,97.092989
RIGHT,B cell,Element 4 / Cluster 23,IL18,5,65.762519
LEFT,B cell,Element 4 / Cluster 23,MMP9,5,157.286928
LEFT,B cell,Element 4 / Cluster 23,IL18,5,305.109008
RIGHT,B cell,Element 4 / Cluster 23,MDC,5,326.240111
LEFT,B cell,Element 4 / Cluster 23,MDC,5,511.845525

;

data cd45;
infile datalines delimiter=',';
input ALAT $ CELL_TYPE $ XXGRPID : $ 13-36 cd45 $ n1 sum1;
datalines ;
LEFT,B cell,Element 4 / Cluster 01,CD45,1,5.042122
RIGHT,B cell,Element 4 / Cluster 01,CD45,4,13.864326
RIGHT,B cell,Element 4 / Cluster 06,CD45,4,405.028771
LEFT,B cell,Element 4 / Cluster 06,CD45,5,39.414534
LEFT,B cell,Element 4 / Cluster 08,CD45,5,672.423284
RIGHT,B cell,Element 4 / Cluster 08,CD45,5,1010.053084
LEFT,B cell,Element 4 / Cluster 11,CD45,1,1.145423
LEFT,B cell,Element 4 / Cluster 12,CD45,5,49.411095
RIGHT,B cell,Element 4 / Cluster 12,CD45,5,55.321253
LEFT,B cell,Element 4 / Cluster 13,CD45,5,622.640166
RIGHT,B cell,Element 4 / Cluster 13,CD45,5,1002.174066
LEFT,B cell,Element 4 / Cluster 14,CD45,1,3.815439
RIGHT,B cell,Element 4 / Cluster 14,CD45,1,10.778137
LEFT,B cell,Element 4 / Cluster 15,CD45,2,8.488739
RIGHT,B cell,Element 4 / Cluster 15,CD45,3,6.792764
LEFT,B cell,Element 4 / Cluster 17,CD45,1,2.039313
LEFT,B cell,Element 4 / Cluster 18,CD45,4,20.873398
RIGHT,B cell,Element 4 / Cluster 18,CD45,5,26.128614
LEFT,B cell,Element 4 / Cluster 23,CD45,4,6.611934
RIGHT,B cell,Element 4 / Cluster 23,CD45,4,7.515591
;

I want help to merge these two data sets when

ALAT CELL_TYPE XXGRPID n1 sum1 are the same in both datasets.

smantha · Posted 07-01-2020 01:54 PM

It is always a bad idea to merge two datasets on decimals (the problem is with precision). You can try rounding the data to second decimal and merge on it in a proc sql step. I do not know of a way to do it in a datastep.

as n example on the where condition you can use

round(a.var1,0.01) = round(b.var1,0.01)

mklangley · Posted 07-01-2020 02:48 PM

On a quick glance through the datasets (thank you, by the way, for providing your data in working DATALINES code), it looks like there aren't any rows where ALAT, CELL_TYPE, XXGRPID, N1, and SUM1 are the same in both datasets (even when rounding SUM1, as @smantha recommended). Do you expect there to be rows that match on those variables? Could you provide more details and an example?

mona4u · Posted 07-01-2020 04:07 PM

for example when I'm merging this row from all dataset

RIGHT,B cell,Element 4 / Cluster 01,IL18,2,5.116604

with this row from cd45

LEFT,B cell,Element 4 / Cluster 01,CD45,1,5.042122

I only need row in the output like this

LEFT,B cell,Element 4 / Cluster 01,CD45,1,5.042122, IL18

mklangley · Posted 07-01-2020 04:37 PM

Those two rows don't match on ALAT (RIGHT vs LEFT), N1 (2 vs 1), or SUM1 (5.116604 vs 5.042122).

How do you determine that they should be merged?

@mona4u wrote:

I want help to merge these two data sets when

ALAT CELL_TYPE XXGRPID n1 sum1 are the same in both datasets.

merging by numeric variables