Hash Object code to transpose data and Join

[raja777pharma]

Hello,

I am new to Hash Object code and i have exist to code to understand and update.

The exist code to work well when  have one unique varible in both data set  and used same unique varible in hash code (USUBJID), but for data sets doen't have one unique varible then this code is not workinng properly.

Below data attached as CSV files.

Input data sets : DM and SUPPDM 

Out Data set : Shoud match the QC_DS data set.

Current Code : The current code is working when data sets have unique varible (USUBJID ) , but above data sets have SUBJID unique varible , and will get unique varible using by statement USUBJID,SUBJID.

I am using USUBJID,SUBJID in by statement but not worked well.

Please help me to udpate this code for to work multiple SUBJECT varible passed to by statment.

The expeccted output is qc_ds_csv file .

data outds;

   length qnam $ 8 temp_idvar_value $ 200;
   
   drop qnam temp_idvar_value;

   *--- QNAMs lookup ---;
   declare hash expected_qnams();
   expected_qnams.defineKey('qnam');
   expected_qnams.defineDone();
   qnam = "SITEIDSS";
   expected_qnams.add();
   qnam = "RACEOTH";
   expected_qnams.add();
   qnam = "RACE1";
   expected_qnams.add();
   qnam = "RACE2";
   expected_qnams.add();
   qnam = "RACE3";
   expected_qnams.add();
   qnam = "RACE4";
   expected_qnams.add();
   qnam = "SUBJGRP";
   expected_qnams.add();
   qnam = "SUBPRT";
   expected_qnams.add();
   qnam = "SUBPVR";
   expected_qnams.add();

   *--- SuppQual lookup ---;
   declare hash supps(hashexp: 12);
   supps.defineKey("SUPP_USUBJID" , 'idvar', 'idvarval', 'qnam');
   supps.defineData('qval');
   supps.defineDone();

   *--- IDVARs lookup ---;
   declare hash idvars();
   idvars.defineKey('idvar');
   idvars.defineDone();
   declare hiter idvar_iter('idvars');

   do until (end);
      do until (last.USUBJID);
         set dm end=end;
         by UUSUBJID;
         if first.USUBJID then do;
            if not end_supp and supp_USUBJID < USUBJID then do until (last.USUBJID);
               set suppdm (rename=(UUSUBJID = SUPP_UUSUBJID USUBJID = SUPP_USUBJID) keep=UUSUBJID USUBJID idvar idvarval qnam qval) end=end_supp;
               by supp_UUSUBJID supp_USUBJID;
               drop rc supp_UUSUBJID supp_USUBJID idvar idvarval qnam qval;
               idvar = kupcase(idvar);
               qnam = kupcase(qnam);

               rc = expected_qnams.find();

               if rc ne 0 and qnam ne '' and not indexw("", qnam) then do;
                  put "ERROR: The SuppQual data set contains an unexpected value of QNAM - " qnam;
               end;
               else do;
                  rc = idvars.add();
                  rc = supps.add();

                  if rc ne 0 then do;
                     put " Multiple SuppQual records detected for USUBJID " supp_USUBJID idvar= idvarval= qnam=;
                     call symput('sps_err_flag', 'Y');
                  end;
               end;
            end;
         end;
         call missing(SITEIDSS, RACEOTH, RACE1, RACE2, RACE3, RACE4, SUBJGRP, SUBPRT, SUBPVR);
         rc = idvar_iter.first();
         do until (rc ne 0);
            if idvar ne '' then do;
               temp_idvar_value = kleft(vvaluex(idvar));

               if temp_idvar_value = '.' then
                  temp_idvar_value = '';
            end;
            else do;
               temp_idvar_value = '';
            end;           
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "SITEIDSS");            
            if rc = 0 then do;               
               if not missing(SITEIDSS) then do;
                  put " There is an attempt to set suppqual SITEIDSS more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                 
                  SITEIDSS = qval;
               end;
            end;
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "RACEOTH");
            if rc = 0 then do;
               if not missing(RACEOTH) then do;
                  put " There is an attempt to set suppqual RACEOTH more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                  
                  RACEOTH = qval;
               end;
            end;           
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "RACE1");           
            if rc = 0 then do;               
               if not missing(RACE1) then do;
                  put " There is an attempt to set suppqual RACE1 more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                 
                  RACE1 = qval;
               end;
            end;           
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "RACE2");           
            if rc = 0 then do;               
               if not missing(RACE2) then do;
                  put " There is an attempt to set suppqual RACE2 more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                 
                  RACE2 = qval;
               end;
            end;            
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "RACE3");            
            if rc = 0 then do;              
               if not missing(RACE3) then do;
                  put " There is an attempt to set suppqual RACE3 more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                  
                  RACE3 = qval;
               end;
            end;           
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "RACE4");           
            if rc = 0 then do;              
               if not missing(RACE4) then do;
                  put " There is an attempt to set suppqual RACE4 more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                 
                  RACE4 = qval;
               end;
            end;            
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "SUBJGRP");           
            if rc = 0 then do;               
               if not missing(SUBJGRP) then do;
                  put " There is an attempt to set suppqual SUBJGRP more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                  
                  SUBJGRP = qval;
               end;
            end;           
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "SUBPRT");           
            if rc = 0 then do;               
               if not missing(SUBPRT) then do;
                  put " There is an attempt to set suppqual SUBPRT more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                  
                  SUBPRT = qval;
               end;
            end;           
            rc = supps.find(key:SUPP_USUBJID, key: kupcase(idvar), key: temp_idvar_value, key: "SUBPVR");           
            if rc = 0 then do;              
               if not missing(SUBPVR) then do;
                  put " There is an attempt to set suppqual SUBPVR more than once for " USUBJID=;
                  call symput('sps_err_flag', 'Y');
               end;
               else do;                 
                  SUBPVR = qval;
               end;
            end;
            rc = idvar_iter.next();
         end;
         output;
      end;
      if USUBJID >= supp_USUBJID then do;
         rc = supps.num_items;

         if rc > 10000 then do;
            rc = idvars.clear();
            rc = supps.clear();
         end;
      end;
   end;
run;

[Kurt_Bremser]

This will cause problems:

      do until (last.USUBJID);
         set dm end=end;
         by UUSUBJID;
         if first.USUBJID then do;

Either the BY will fail because UUSUBJID is not there, or you will get messages about first.USUBJID and last.USUBJID.

[raja777pharma]

Hi,

I have used correct USUBJID in by statment ,howevver would like to use the same code for by varibles USUBJID and SUBJID

if i use 'by usubjid subjid ' then no output data is not macthed with expected one.

Thank you,

[Kurt_Bremser]

This will also not work:

         if first.USUBJID then do;
            if not end_supp and supp_USUBJID < USUBJID then do until (last.USUBJID);

In the first IF, you check for FIRST.USUBJID; if you have more than one obs per USUBJID in dataset DM (which you have), then LAST.USUBJID will be constantly FALSE, causing the loop to run until the end of the dataset is reached and the data step terminates. This part will only work if USUBJID is always unique in dataset DM, but in that case it will read only one observation from SUPPDM (as the UNTIL will iterate at least once, but find that the termination condition is true at the end of the first iteration).

I suggest that you do this in two steps:

proc transpose
  data=suppdm
  out=supp_trans (drop=_name_)
;
by usubjid subjid;
id qnam;
var qval;
run;

data outds;
merge
  dm (in=indm)
  supp_trans
;
by usubjid subjid;
if indm;
run;

You can apply filters where appropriate; the TRANSPOSE will make sure in itself that only unique QNAMs exist for any given USUBJID/SUBJID combination.