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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.