CODEWITHSUNDEEP
sas
JPOLIVA
JPOLIVA

Reputation: 65

SAS - MD5 entire table

I'm calculating a hash (md5) row by row of an entire table, 1 hash for each row. The table is selected by the user with a prompt (&sel_lib for the selected lib and &sel_tab for the table).

1st i get my columns from sas.help.vcolumn and concatenated all to a attribute called lista:

data contents;
    do until (last.memname);
        set sashelp.vcolumn;
        where upcase(libname)="&sel_livraria"
        and upcase(memname)="&sel_tabela";
        by libname memname varnum;
        length lista $&max_len ; /* sum(len col) + number of col */
        lista=catx(',',lista,name);
    end;
    keep libname memname varnum lista;
run;

2nd i write that value in another table, because i need it for other operations not related with this issue:

proc sql;
    update md5_table
    set nom_colunas=(
        select lista
        from contents)
    where libname="&sel_lib"
    and memname="&sel_tab";
quit;

3rd Then i pass that concatenated columns to macro var scolunas

proc sql;
    select columns
    into :scolunas
    from md5_table
    where libname="&sel_lib"
    and memname="&sel_tab";
quit;

Then i use it to run my hash in my data step

data want;
    livraria="&sel_lib";
    tabela="&sel_tab";
    length check $32.;
    format check $hex32.
    set &sel_livraria..&sel_tabela.;
    check = md5(cats(&scolunas));
    hash = put(check,$hex32.);
    keep livraria tabela hash;
    put hash;
    put _all_;
run;

My problem is that i need to compare the output with the output of a same table in another server (plataform migration), so i need a reference to compare both.

I prefer to do that by adding a row number of the source table (&sel_lib &sel_tab) to my data set. Any way to do that? A more complex one would be adding the concatenate PK values to it.

Tks in advance.

Upvotes: 0

Views: 400

Answers (1)

Richard
Richard

Reputation: 27508

STRIP will remove leading and trailing spaces of only one expression (i.e. variable) and thus accepts only a single argument.

You want your comma separated variable list concatenated as input to MD5. CATS() will concatenate variables and implicitly strip.

...
    check = md5(cats(&scolumnlist));
...

If you think that data values may in some edge cases cause a concatenation that is the same in different contexts use the CATX(<sep>,....) to explicitly separate the fields when concatenated.

Example:

A   B    C   CATS()   CATX('|',)
--  ---  --  ------   ----------
PP  QQ   RR  PPQQRR   PP|QQ|RR
P   PQQ  RR  PPQQRR   P|PQQ|RR

Upvotes: 2

Related Questions