A question about when use hash

[Lee_wan]

Hi all,

what I have :

data WORK.SUPPCM;
  infile datalines dsd truncover;
  input USUBJID $ 1 IDVARVAL $ 3-4 QNAM $ 6-9 QVAL :$200.;
  label USUBJID="Unique Subject Identifier" IDVARVAL="Identifying Variable Value" QNAM="Qualifier Variable Name" QVAL="Data Value";
datalines;
a 1  ATC  AAA
a 1  ATC3 BBB
a 10 ATC  OLISM
a 10 ATC3 GASTRO
a 11 ATC  CSYSTEM
a 11 ATC3 SFFECTS
a 12 ATC  VARIOUS
a 13 ATC  ALISM
a 13 ATC3 DRESOPHAGERD
a 2  ATC  ANTNTS
a 2  ATC3 ANTIMES
a 3  ATC  ANTINEOPLLATING AGENTS
a 3  ATC3 OTHASTS
a 4  ATC  ANTINGENTS
a 4  ATC3 ANTIMITES
a 5  ATC  CARDEM
b 1  ATC  FF
b 1  ATC3 DDFAS
b 10 ATC  OLIFASFSM
b 10 ATC3 GASFTRO
b 11 ATC  CSYASTEM
b 11 ATC3 SFFSDECTS
b 12 ATC  VAFRIOUS
b 13 ATC  ALISM
b 13 ATC3 DRFESOPHAGERD
b 14 ATC  ALIFSM
b 14 ATC3 DRFDESOPHAGERD
b 2  ATC  ANTNSDFTS
b 2  ATC3 ANTIMES
b 3  ATC  ANTINEOPLLATING AGENTS
b 3  ATC3 OTHASDSTS
b 4  ATC  ANTIFNGENTS
b 4  ATC3 ANTIMITES
b 5  ATC  CARDSDEM
b 5  ATC3 CARDSDEM
b 6  ATC  fssdf
b 6  ATC3 FSDFD
;;;;
run;

data WORK.CM;
  infile datalines dsd truncover;
  input USUBJID $ 1 CMSEQ;
  label USUBJID="Unique Subject Identifier" CMSEQ="Sequence Number";
datalines;
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
a 9
a 10
a 11
a 12
a 13
b 1
b 2
b 3
b 4
b 5
b 6
b 7
b 8
b 9
b 10
b 11
b 12
b 13
b 14
b 15
b 16
;;;;
run;

and my code is as below:

data cm1;
if _n_=1 then do;
  dcl hash h();
  h.definekey('usubjid','cmseq');
  h.definedata('usubjid','cmseq','atc','atc3');
  h.definedone();
end;
  do until(last.idvarval);
    set suppcm;
    by usubjid idvarval;
    if qnam='ATC' then atc=qval;
    if qnam='ATC3' then atc3=qval;
  end;
  cmseq=input(idvarval,best.);
  h.add();


  set cm;
  call missing(atc,atc3);
  rc=h.find();

  keep usubjid cmseq atc atc3 ;
run;

What puzzled me is that atc and atc3 are all missing when cmseq=2,3,4,5,6,7,8,9.

Is something wrong with my code？Please help me. Thanks.

[PeterClemmensen]

here is how I would do it..

proc transpose data=suppcm out=suppcm_wide(drop=_:);
    by USUBJID IDVARVAL;
    id QNAM;
    var QVAL;
run;

data cm1;
  format usubjid cmseq atc atc3 a;
  if _n_=1 then do;
    dcl hash h();
    h.definekey('usubjid','cmseq');
    h.definedata('usubjid','cmseq','atc','atc3');
    h.definedone();

    do until(lr);
      set suppcm_wide end=lr;
      cmseq=input(idvarval, best.);
      rc=h.add();
    end;
  end;

  set cm;
  call missing(atc,atc3);
  if h.find()=0 then a=1;

  keep usubjid cmseq atc atc3 a;
run;

[Lee_wan]

Hi draycut,
I don't know why my last message was deleted.

Thank you so much for your response.

But my goal is to use hash to complete this by only one data process.

So I don't want to use transpose before the data process.

[PeterClemmensen]

No problem. Then do

data cm1;
    format usubjid cmseq atc atc3 ;
    if _N_=1 then do;
        dcl hash h(multidata:'Y');
        h.definekey('usubjid', 'cmseq');
        h.definedata('qnam', 'qval');
        h.definedone();

        do until(lr);
          set suppcm end=lr;
          cmseq=input(idvarval, best8.);
          rc=h.add();
        end;
    end;

    set cm;

    do while (h.do_over()=0);
       if      qnam='ATC'  then atc=qval;
       else if qnam='ATC3' then atc3=qval;
    end;

    keep usubjid cmseq atc atc3 ;
run;

[Lee_wan]

Thanks~
But I run the code on my new data as above，the result look like not right.

[PeterClemmensen]

What differs from the way you want it to look? Please be more specific

[Lee_wan]

Please see the detail as above.

It looks like only map the first record.

Thanks

[PeterClemmensen · Accepted solution]

Just to be clear. When I run this

data WORK.SUPPCM;
  infile datalines dsd truncover;
  input USUBJID $ 1 IDVARVAL $ 3-4 QNAM $ 6-9 QVAL :$200.;
  label USUBJID="Unique Subject Identifier" IDVARVAL="Identifying Variable Value" QNAM="Qualifier Variable Name" QVAL="Data Value";
datalines;
a 1  ATC  AAA
a 1  ATC3 BBB
a 10 ATC  OLISM
a 10 ATC3 GASTRO
a 11 ATC  CSYSTEM
a 11 ATC3 SFFECTS
a 12 ATC  VARIOUS
a 13 ATC  ALISM
a 13 ATC3 DRESOPHAGERD
a 2  ATC  ANTNTS
a 2  ATC3 ANTIMES
a 3  ATC  ANTINEOPLLATING AGENTS
a 3  ATC3 OTHASTS
a 4  ATC  ANTINGENTS
a 4  ATC3 ANTIMITES
a 5  ATC  CARDEM
b 1  ATC  FF
b 1  ATC3 DDFAS
b 10 ATC  OLIFASFSM
b 10 ATC3 GASFTRO
b 11 ATC  CSYASTEM
b 11 ATC3 SFFSDECTS
b 12 ATC  VAFRIOUS
b 13 ATC  ALISM
b 13 ATC3 DRFESOPHAGERD
b 14 ATC  ALIFSM
b 14 ATC3 DRFDESOPHAGERD
b 2  ATC  ANTNSDFTS
b 2  ATC3 ANTIMES
b 3  ATC  ANTINEOPLLATING AGENTS
b 3  ATC3 OTHASDSTS
b 4  ATC  ANTIFNGENTS
b 4  ATC3 ANTIMITES
b 5  ATC  CARDSDEM
b 5  ATC3 CARDSDEM
b 6  ATC  fssdf
b 6  ATC3 FSDFD
;;;;
run;

data WORK.CM;
  infile datalines dsd truncover;
  input USUBJID $ 1 CMSEQ;
  label USUBJID="Unique Subject Identifier" CMSEQ="Sequence Number";
datalines;
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
a 9
a 10
a 11
a 12
a 13
b 1
b 2
b 3
b 4
b 5
b 6
b 7
b 8
b 9
b 10
b 11
b 12
b 13
b 14
b 15
b 16
;;;;
run;

data cm1;
    format usubjid cmseq atc atc3 ;
    if _N_=1 then do;
        dcl hash h(multidata:'Y');
        h.definekey('usubjid', 'cmseq');
        h.definedata('qnam', 'qval');
        h.definedone();

        do until(lr);
          set suppcm end=lr;
          cmseq=input(idvarval, best8.);
          rc=h.add();
        end;
    end;

    set cm;

    do while (h.do_over()=0);
       if      qnam='ATC'  then atc=qval;
       else if qnam='ATC3' then atc3=qval;
    end;

    keep usubjid cmseq atc atc3 ;
run;

This is my result

Obs usubjid cmseq atc                     atc3 
1   a       1     AAA                     BBB 
2   a       2     ANTNTS                  ANTIMES 
3   a       3     ANTINEOPLLATING AGENTS  OTHASTS 
4   a       4     ANTINGENTS              ANTIMITES 
5   a       5     CARDEM   
6   a       6       
7   a       7       
8   a       8       
9   a       9       
10  a       10    OLISM                   GASTRO 
11  a       11    CSYSTEM                 SFFECTS 
12  a       12    VARIOUS                   
13  a       13    ALISM                   DRESOPHAGERD 
14  b       1     FF                      DDFAS 
15  b       2     ANTNSDFTS               ANTIMES 
16  b       3     ANTINEOPLLATING AGENTS  OTHASDSTS 
17  b       4     ANTIFNGENTS             ANTIMITES 
18  b       5     CARDSDEM                CARDSDEM 
19  b       6     fssdf                   FSDFD 
20  b       7        
21  b       8        
22  b       9        
23  b       10    OLIFASFSM               GASFTRO 
24  b       11    CSYASTEM                SFFSDECTS 
25  b       12    VAFRIOUS                 
26  b       13    ALISM                   DRFESOPHAGERD 
27  b       14    ALIFSM                  DRFDESOPHAGERD 
28  b       15        
29  b       16        

[Lee_wan]

Oh！！！Thank you！！Thank you so much！！
It‘s work！！ This bothered me for a long time！
How do you learn to use hash? Can you give some books or methods？

[PeterClemmensen]

Anytime 🙂

I sure do. Obviously the SAS Hash Object Documentation is a good place to start.

For a good book, check out Data Management Solutions Using SAS® Hash Table Operations: A Business Intelligence Case Study

[Lee_wan]

Got it and thanks again!