Solved: how to replace missing values with values present by merging them

vraj1 · Posted 05-02-2018 07:41 AM

I have 2 datasets inc and inc2. inc2 has few variables which are missing and i want to replace those data which are missing from inc. I tried the below code but it didnt work.

data exm_final;
   merge  inc(in=b keep=usubjid lbtestcd lbdtc lbseq visit visitnum) inc2(in=a);
   by usubjid lbtestcd LBDTC;
   if a;
run;

any help

data WORK.INC;
  infile datalines dsd truncover;
  input USUBJID:$50. LBSEQ:32. LBTESTCD:$8. VISITNUM:32. VISIT:$100. LBDTC:$50.;
datalines4;
 1078,22,FSH,1,Visit 1   ,2017-05-30T09:48
 1078,23,LH,1,Visit 1   ,2017-05-30T09:48
 1078,25,PROLCTN,1,Visit 1   ,2017-05-30T09:48
 1078,26,TESTOS,1,Visit 1   ,2017-05-30T09:48
 1093,22,FSH,1,Visit 1   ,2017-07-03T10:23
 1093,23,LH,1,Visit 1   ,2017-07-03T10:23
 1093,25,PROLCTN,1,Visit 1   ,2017-07-03T10:23
 1093,26,TESTOS,1,Visit 1   ,2017-07-03T10:23
;;;;


data WORK.INC2;
  infile datalines dsd truncover;
  input IAVISIT:$40. USUBJID:$50. LBSEQ:32. LBTESTCD:$8. VISITNUM:32. VISIT:$100. LBDTC:$50. BL04DT:YYMMDD10. IAVISITN:32.;
datalines4;
W0102/C02/BASE03, 1078,,ALB,,,,2017-11-27,12
C01/BASE02, 1078,46,ALB,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,104,ALB,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,ALP,,,,2017-11-27,12
C01/BASE02, 1078,70,ALP,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,133,ALP,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,ALT,,,,2017-11-27,12
C01/BASE02, 1078,71,ALT,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,134,ALT,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,AST,,,,2017-11-27,12
C01/BASE02, 1078,72,AST,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,135,AST,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,BASO,,,,2017-11-27,12
C01/BASE02, 1078,48,BASO,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,106,BASO,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,BASOLE,,,,2017-11-27,12
C01/BASE02, 1078,49,BASOLE,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,107,BASOLE,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,BILDIR,,,,2017-11-27,12
C01/BASE02, 1078,73,BILDIR,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,136,BILDIR,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,BILI,,,,2017-11-27,12
C01/BASE02, 1078,74,BILI,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,137,BILI,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,BUN,,,,2017-11-27,12
C01/BASE02, 1078,64,BUN,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,127,BUN,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,CA,,,,2017-11-27,12
C01/BASE02, 1078,43,CA,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,101,CA,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,CHOL,,,,2017-11-27,12
C01/BASE02, 1078,66,CHOL,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,129,CHOL,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,CREAT,,,,2017-11-27,12
C01/BASE02, 1078,65,CREAT,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,128,CREAT,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,CRP,,,,2017-11-27,12
C01/BASE02, 1078,63,CRP,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,126,CRP,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,EOS,,,,2017-11-27,12
C01/BASE02, 1078,50,EOS,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,108,EOS,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,EOSLE,,,,2017-11-27,12
C01/BASE02, 1078,51,EOSLE,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,109,EOSLE,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,FSH,,,,2017-11-27,12
W03/C03/BASE04, 1078,121,FSH,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,GGT,,,,2017-11-27,12
C01/BASE02, 1078,75,GGT,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,138,GGT,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,GLUC,,,,2017-11-27,12
C01/BASE02, 1078,47,GLUC,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,105,GLUC,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,HCT,,,,2017-11-27,12
C01/BASE02, 1078,52,HCT,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,110,HCT,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,HDL,,,,2017-11-27,12
C01/BASE02, 1078,67,HDL,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,130,HDL,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,HGB,,,,2017-11-27,12
C01/BASE02, 1078,53,HGB,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,111,HGB,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,K,,,,2017-11-27,12
C01/BASE02, 1078,44,K,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,102,K,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,LDH,,,,2017-11-27,12
C01/BASE02, 1078,42,LDH,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,92,LDH,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,LDL,,,,2017-11-27,12
C01/BASE02, 1078,68,LDL,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,131,LDL,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,LH,,,,2017-11-27,12
W03/C03/BASE04, 1078,122,LH,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,LYM,,,,2017-11-27,12
C01/BASE02, 1078,54,LYM,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,112,LYM,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,LYMLE,,,,2017-11-27,12
C01/BASE02, 1078,55,LYMLE,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,113,LYMLE,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,MONO,,,,2017-11-27,12
C01/BASE02, 1078,56,MONO,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,114,MONO,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,MONOLE,,,,2017-11-27,12
C01/BASE02, 1078,57,MONOLE,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,115,MONOLE,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,NEUT,,,,2017-11-27,12
C01/BASE02, 1078,58,NEUT,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,116,NEUT,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,NEUTLE,,,,2017-11-27,12
C01/BASE02, 1078,59,NEUTLE,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,117,NEUTLE,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,PLAT,,,,2017-11-27,12
C01/BASE02, 1078,60,PLAT,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,118,PLAT,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,PROLCTN,,,,2017-11-27,12
W03/C03/BASE04, 1078,124,PROLCTN,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,RBC,,,,2017-11-27,12
C01/BASE02, 1078,61,RBC,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,119,RBC,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,SODIUM,,,,2017-11-27,12
C01/BASE02, 1078,45,SODIUM,6,Visit 6   ,2017-04-03T11:36,2017-11-27,6
W03/C03/BASE04, 1078,103,SODIUM,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1078,,TESTOS,,,,2017-11-27,12
W03/C03/BASE04, 1078,125,TESTOS,13,Visit 13    ,2017-11-27T11:58,2017-11-27,24
W0102/C02/BASE03, 1093,,ALT,,,,2017-12-28,12
C01/BASE02, 1093,71,ALT,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,143,ALT,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,AST,,,,2017-12-28,12
C01/BASE02, 1093,72,AST,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,144,AST,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,BASO,,,,2017-12-28,12
C01/BASE02, 1093,48,BASO,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,115,BASO,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,BASOLE,,,,2017-12-28,12
C01/BASE02, 1093,49,BASOLE,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,116,BASOLE,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,BILDIR,,,,2017-12-28,12
C01/BASE02, 1093,73,BILDIR,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,145,BILDIR,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,BILI,,,,2017-12-28,12
C01/BASE02, 1093,74,BILI,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,146,BILI,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,BUN,,,,2017-12-28,12
C01/BASE02, 1093,64,BUN,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,136,BUN,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,CA,,,,2017-12-28,12
C01/BASE02, 1093,43,CA,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,110,CA,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,CHOL,,,,2017-12-28,12
C01/BASE02, 1093,66,CHOL,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,138,CHOL,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,CREAT,,,,2017-12-28,12
C01/BASE02, 1093,65,CREAT,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,137,CREAT,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,CRP,,,,2017-12-28,12
C01/BASE02, 1093,63,CRP,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,135,CRP,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,EOS,,,,2017-12-28,12
C01/BASE02, 1093,50,EOS,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,117,EOS,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,EOSLE,,,,2017-12-28,12
C01/BASE02, 1093,51,EOSLE,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,118,EOSLE,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,FSH,,,,2017-12-28,12
W03/C03/BASE04, 1093,130,FSH,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,GGT,,,,2017-12-28,12
C01/BASE02, 1093,75,GGT,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,147,GGT,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,GLUC,,,,2017-12-28,12
C01/BASE02, 1093,47,GLUC,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,114,GLUC,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,HCT,,,,2017-12-28,12
C01/BASE02, 1093,52,HCT,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,119,HCT,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,HDL,,,,2017-12-28,12
C01/BASE02, 1093,67,HDL,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,139,HDL,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,HGB,,,,2017-12-28,12
C01/BASE02, 1093,53,HGB,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,120,HGB,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,K,,,,2017-12-28,12
C01/BASE02, 1093,44,K,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,111,K,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,LDH,,,,2017-12-28,12
C01/BASE02, 1093,42,LDH,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,101,LDH,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,LDL,,,,2017-12-28,12
C01/BASE02, 1093,68,LDL,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,140,LDL,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,LH,,,,2017-12-28,12
W03/C03/BASE04, 1093,131,LH,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,LYM,,,,2017-12-28,12
C01/BASE02, 1093,54,LYM,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,121,LYM,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,LYMLE,,,,2017-12-28,12
C01/BASE02, 1093,55,LYMLE,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,122,LYMLE,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,MONO,,,,2017-12-28,12
C01/BASE02, 1093,56,MONO,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,123,MONO,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,MONOLE,,,,2017-12-28,12
C01/BASE02, 1093,57,MONOLE,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,124,MONOLE,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,NEUT,,,,2017-12-28,12
C01/BASE02, 1093,58,NEUT,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,125,NEUT,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,NEUTLE,,,,2017-12-28,12
C01/BASE02, 1093,59,NEUTLE,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,126,NEUTLE,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,PLAT,,,,2017-12-28,12
C01/BASE02, 1093,60,PLAT,6,Visit 6   ,2017-05-05T12:18,2017-12-28,6
W03/C03/BASE04, 1093,127,PLAT,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,PROLCTN,,,,2017-12-28,12
W03/C03/BASE04, 1093,133,PROLCTN,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
W0102/C02/BASE03, 1093,,TESTOS,,,,2017-12-28,12
W03/C03/BASE04, 1093,134,TESTOS,13,Visit 13    ,2017-12-28T12:11,2017-12-28,24
;;;;

test data below

RW9 · Posted 05-02-2018 07:52 AM

A simple couple of line just to illustrate is fine on the test data. Anyways, rename the in variables from the smaller, then do an if or coalesce:

data exm_final (drop=tmp_lbtestcd tmp_lbdtc...);
   merge  inc (in=b keep=usubjid lbtestcd lbdtc lbseq visit visitnum 
                    rename=(lbtestcd=tmp_lbtestcd lbdtc=tmp_lbdtc...)) 
          inc2 (in=a);
   by usubjid lbtestcd LBDTC;
   if lbtestcd="" then lbtestcd=tmp_lbtestcd;
   if lbdtc="" then lbdtc=tmp_lbdtc;
   ...;

   if a;
run;

You could maybe to it with the update part of datastep;

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202975.htm

Up to you.

View solution in original post

RW9 · Posted 05-02-2018 07:52 AM

A simple couple of line just to illustrate is fine on the test data. Anyways, rename the in variables from the smaller, then do an if or coalesce:

data exm_final (drop=tmp_lbtestcd tmp_lbdtc...);
   merge  inc (in=b keep=usubjid lbtestcd lbdtc lbseq visit visitnum 
                    rename=(lbtestcd=tmp_lbtestcd lbdtc=tmp_lbdtc...)) 
          inc2 (in=a);
   by usubjid lbtestcd LBDTC;
   if lbtestcd="" then lbtestcd=tmp_lbtestcd;
   if lbdtc="" then lbdtc=tmp_lbdtc;
   ...;

   if a;
run;

You could maybe to it with the update part of datastep;

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202975.htm

Up to you.

how to replace missing values with values present by merging them

Re: how to replace missing values with values present by merging them

Re: how to replace missing values with values present by merging them

how to replace missing values with values present by merging them

Re: how to replace missing values with values present by merging them

Re: how to replace missing values with values present by merging them

The 2025 SAS Hackathon has begun!

SAS Training: Just a Click Away