topic Re: creating one to many merge- for lab shift table in SAS Programming

creating one to many merge- for lab shift table

sitaram — Wed, 06 Nov 2024 15:19:04 GMT

i have two datasets. 1. dum1 2. all_freqs

data dum1;length btoxgr $7.;
btoxgr='Grade 0'; output;
btoxgr='Grade 1'; output;
btoxgr='Grade 2'; output;
btoxgr='Grade 3'; output;
btoxgr='Grade 4'; output;
btoxgr='T_Btoxg'; output;
run;

data all_freqs;
PARCAT3='HAEMATOLOGY';btoxgr='Grade0';TRT01A='LZA';PARAMCD='GRAN';cnt=12;output;
PARCAT3='HAEMATOLOGY';btoxgr='Grade2';TRT01A='LZA';PARAMCD='GRAN';cnt=20;output;
PARCAT3='HAEMATOLOGY';btoxgr='Grade1';TRT01A='LZA';PARAMCD='LYMP';cnt=19;output;
PARCAT3='HAEMATOLOGY';btoxgr='Grade2';TRT01A='LZA';PARAMCD='KKSP';cnt=90;output;

PARCAT3='LIVER AND KIDNEY';btoxgr='Grade0';TRT01A='LZA';PARAMCD='PLAT';cnt=12;output;
PARCAT3='LIVER AND KIDNEY';btoxgr='Grade2';TRT01A='LZA';PARAMCD='WBC';cnt=22;output;
PARCAT3='LIVER AND KIDNEY';btoxgr='Grade1';TRT01A='LZA';PARAMCD='WBC';cnt=24;output;
PARCAT3='LIVER AND KIDNEY';btoxgr='Grade3';TRT01A='LZA';PARAMCD='AST';cnt=11;output;
run;

I would need a dataset that can take all the rows of all_freqs merged with dum1 in all possible combination i.e. in all_freqs dataset under 1 parmcd we have btoxgr='Grade0' present only, in that case all other grades must be coming for the same paramcd with values 0 in cnt variable.

tried using the below code, but nor working.

data dummy;
merge all_freqs(KEEP=parcat3 param paramcd trt01an trt01a btoxgr IN=A) dum1(in=b); */
BY BTOXGR;if a or b;
RUN;

any help?

Re: creating one to many merge- for lab shift table

mkeintz — Wed, 06 Nov 2024 15:33:42 GMT

I do not understand what you want the output to look like. Please show what it would look like for the sample data you have provided.

Re: creating one to many merge- for lab shift table

data_null__ — Wed, 06 Nov 2024 16:04:11 GMT

How about a multlabel format.

data all_freqs;
PARCAT3='HAEMATOLOGY';btoxgr='Grade0';TRT01A='LZA';PARAMCD='GRAN';cnt=12;output;
PARCAT3='HAEMATOLOGY';btoxgr='Grade2';TRT01A='LZA';PARAMCD='GRAN';cnt=20;output;
PARCAT3='HAEMATOLOGY';btoxgr='Grade1';TRT01A='LZA';PARAMCD='LYMP';cnt=19;output;
PARCAT3='HAEMATOLOGY';btoxgr='Grade2';TRT01A='LZA';PARAMCD='KKSP';cnt=90;output;

PARCAT3='LIVER AND KIDNEY';btoxgr='Grade0';TRT01A='LZA';PARAMCD='PLAT';cnt=12;output;
PARCAT3='LIVER AND KIDNEY';btoxgr='Grade2';TRT01A='LZA';PARAMCD='WBC';cnt=22;output;
PARCAT3='LIVER AND KIDNEY';btoxgr='Grade1';TRT01A='LZA';PARAMCD='WBC';cnt=24;output;
PARCAT3='LIVER AND KIDNEY';btoxgr='Grade3';TRT01A='LZA';PARAMCD='AST';cnt=11;output;
run;
proc sort;
   by parcat3 paramcd trt01a;
   run;

proc format;
   value $txg(notsorted multilabel)
      'Grade0' = 'Grade 0'
      'Grade1' = 'Grade 1'
      'Grade2' = 'Grade 2'
      'Grade3' = 'Grade 3'
      'Grade4' = 'Grade 4'
      'Grade0'-'Grade4' = 'Total'
      ;
   quit;

proc summary data=all_freqs nway missing completetypes;
   by parcat3 paramcd trt01a;
   class btoxgr / mlf order=data preloadfmt;
   format btoxgr $txg.;
   freq cnt;
   output out=summary;
   run;
proc print;
   run;