BookmarkSubscribeRSS Feed
mona4u
Lapis Lazuli | Level 10

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. 

 

 

 

 

4 REPLIES 4
smantha
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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. 

 


 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 658 views
  • 0 likes
  • 3 in conversation