topic Re: How to apply SHA256 hash to a column of a data set ? in SAS Programming

How to apply HMAC-SHA256 hash to a column of a data set ?

skalamat — Fri, 02 Feb 2018 19:36:42 GMT

Hello Community,

The SAS script below extracts 50 obs from a data set, then hashes with md5 2 cols of it, namely, first and last name and then calls a proc, that carries an implementation of SHA256, from a macro to hash the ssn column before it zips the final text file. The proc contains @FriedEgg's brain child and it lives in a file called SHA256AlgoCall.sas that's attached to this thread (well, we changed SHA1 to SHA256 in the proc but we claim no credit for the code development).

A submit block cannot be directly placed in a macro, hence the %Include of SHA256AlgoCall.sas inside macro fldValueSHA256. If the macro has been setup correctly and our logic to package proc groovy of @FriedEgg inside a separate sas file makes sense, we would like to know what's the syntax that calls the macro in the data step to successfully hash the ssn column.

If our script logic to call the hash algorithm is wrong how should we think to make this happen ?

We made great effort to reach this point and we're still in newbie aggressive learning mode.

Server is running SAS 9.3 on AIX 64, the client runs SAS 9.4 on Win10 x64 and the two are VPN connected

We appreciate your time to help us.

*******************************************************************************************************************************

%let dir1=%str(/new/dirct/loct/userLoc/logfiles/);
%let pgmname=%str(SchdDent_20170615);
%let date=&sysdate;
%let time=&systime;
%let ext1=%str(log);
%let break=%str(-);
%let final1=&dir1&pgmname&date&break&time..&ext1;
%put &final1;

%let key1 = %nrstr(kd94hf93k423kf44&pfkkdhi9sl3r4s00) ;

proc printto log="&final1";
run; quit;

options nocenter ls=132 ps=32000 obs=50 compress=char;

   libname in3 '/new/dirct/loct/userLoc/dss20170615/';


*Macro to read in one month of Sched Dent;
%macro readSchdDent(fy);
   libname inscen&fy. "/mainDb/publs/SchdDent/";

   data force schdDent&fy.;

   set inscen&fy..fy&fy.(keep = APPT_DT APPT_TIME SSN LASTNAME FIRSTNAME HIPAA_TAX1 HIPAA_TAX2 HIPAA_TAX3 HIPAA_TAX4 HIPAA_TAX5 SLOT_KEY FY FM);
           if FM='01';
   run;

   proc append data = schdDent&fy. base = in3.schdDent_step1 (compress=char) force;
   run;

   proc datasets;
       delete schdDent&fy.;
   run;

%mend readSchdDent;

%readSchdDent(16);
%readSchdDent(17);

%macro fldValueSHA256 (key, fldVal);

   %include "/new/dirct/loct/userLoc/SHA256AlgoCall.sas" /lrecl=700;
   run;
   &base64hmacsha256
%mend fldValueSHA256;

data in3.schdDent_step1;
   modify in3.schdDent_step1;

   ssn = %fldValueSHA256 (key=key1, fldVal=ssn);

   /*ssn = put(md5(upcase(trim(coalesce(ssn,''))) || '^' || '703447'),$hex32.)*/

   lastname = put(md5(upcase(trim(coalesce(lastname,''))) || '^' || '703447'),$hex32.);
   firstname = put(md5(upcase(trim(coalesce(firstname,''))) || '^' || '703447'),$hex32.);

run;

filename out1 '/new/dirct/loct/userLoc/dss20170615/schdDent.txt';

proc export data=in3.schdDent_step1
   outfile=out1
   dbms=dlm
   replace;
   delimiter='|';
run;
/**/
/**compresses a file w/o encryption*/
/**creates a package.;*/
/*ods package(arch) open nopf;*/
/**/
/**adds the txt file to the package.;*/
/*ods package(arch) add file='/new/dirct/loct/userLoc/dss20170615/schdDent.txt';*/
/**/
/**compresses the package.;*/
/*ods package(arch) publish archive properties (archive_name='schdDent.zip' archive_path='/new/dirct/loct/userLoc/dss20170615');*/
/**/
/**closes the package.;*/
/*ods package(arch) close;*/
/**/
/**deletes the text file.;*/
/*data _null_;*/
/*   rc = fdelete('schdDent.txt');*/
/*run;*/

/*compresses a file with encryption by calling pkzip (available in AIX) */

data _null_;
   call system ('pkzipc -add -passphrase=$@asdfsdf!@@# /new/dirct/loct/userLoc/dss20170615/schdDent.zip /new/dirct/loct/userLoc/dss20170615/schdDent.txt');
run;

Re: How to apply SHA256 hash to a column of a data set ?

LinusH — Fri, 02 Feb 2018 07:31:38 GMT

Perhaps I'm missing something here, but why aren't you using the institute supplied SHA256 function?
http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=lefunctionsref&docsetTarget=p04sqiymw1a6unn1uvh943eudcvz.htm&locale=en

Re: How to apply HMAC-SHA256 hash to a column of a data set ?

skalamat — Fri, 02 Feb 2018 19:38:15 GMT

Thanks for your prompt response LinusH.

Please correct us if we're wrong on the following thought:

As mentioned on our original post the AIX server is running 9.3 version of SAS and the Win10 client has installed SAS 9.4; to be exact SAS EG 7.15 HF2 (7.100.5.6112) (64-bit) and SAS for Windows TS Level 1M3.

We're aware of the Institute's internally developed SHA256 , however, we were under the impression it'll be available only if the server side of SAS is on version 9.4.x, which isn't the case for us.

We experimented calling SHA256 running a short snippet of code against our AIX server from the URL you provided but we get the error shown below:

ERROR 68-185: The function SHA256 is unknown, or cannot be accessed.

Please advice if we're missing something.

Thanks again,

S-

Re: How to apply SHA256 hash to a column of a data set ?

ChrisHemedinger — Fri, 02 Feb 2018 15:18:42 GMT

Rick Langston wrote a paper that shows how to implement SHA256 in DATA step. Not for the faint of heart, but you might find it useful.

Re: How to apply SHA256 hash to a column of a data set ?

FriedEgg — Fri, 02 Feb 2018 18:03:13 GMT

The main problem is that you have not built something with PROC GROOVY that you can call as a function from inside a data step. It would also be good to note here that what you have implemented is HMAC SHA-256 like in my gist. This is very different from SHA-256 and thus the built-in function in SAS 9.4, SHA256 function recommended by @LinusH doesn't apply and neither does the code from Rick on behalf of @ChrisHemedinger. SAS 9.4 does however have a built-in function that duplicates this functionality, which is the SHA256HMACHEX Function. This all leads to the question... Do you actually want HMAC SHA-256 or just SHA-256?

Re: How to apply HMAC-SHA256 hash to a column of a data set ?

skalamat — Fri, 02 Feb 2018 19:39:31 GMT

Thanks for catching our error FriedEgg.

Our goal is the implementation of HMACSHA256 (changed the thread's Subject to reflect the right algorithm) instead of plain SHA256.

Unfortunately, the SAS server is running on version 9.3 (not 9.4), so, it is our understanding we can't use SHA256HMACHEX Function.

Would you advice to call PROC GROOVY from a SAS Macro function which, in turn, we can use in the data step to hash the ssn column?

If not, how else would you recommend hashing the ssn?

Appreciate your input.

S-

Re: How to apply HMAC-SHA256 hash to a column of a data set ?

FriedEgg — Fri, 02 Feb 2018 21:26:00 GMT

* writing this way to a file for ease of posting.  you would have it as a real file;
filename inc temp;
data _null_;
file inc;
input @;
put _infile_;
cards4;
filename cp temp;
proc groovy classpath=cp;

add sasjar="commons_codec" version="1.7.0.0_SAS_20121211183158"; *version is specific to SAS Installation and may differ from this;

submit parseonly;
 
import javax.crypto.Mac
import javax.crypto.spec.SecretKeySpec
import org.apache.commons.codec.binary.Base64

class SASHash { 
    String base64hmacsha256(String key, String message) {
        def mac = Mac.getInstance("HmacSHA256")
        mac.init(new SecretKeySpec(key.getBytes(), "HmacSHA256"))
        def hash = Base64.encodeBase64String(mac.doFinal(message.getBytes()))
        return hash
    }
}
 
endsubmit;
 
quit;

options set=classpath "%sysfunc(pathname(cp,f))";
;;;;
run;

* general purpose macro can be expanded to include additional methods;
%macro SASHash(action,key=key,message=message,return=hash,include=inc);
%goto &action;

%compile:
%include &include;
%return;

%declare:
if _n_=1 then declare javaobj SASHash('SASHash');
%return;

%base64hmacsha256:
SASHash.callStringMethod("&action",strip(&key),strip(&message),&return);
%return;
%mend;

%let key = sdfoij3242039sdflkj3r23;
%SASHash(compile,include=inc)
data class;
    %SASHash(declare)
	length hashname $ 128;
	set sashelp.class;
	%SASHash(base64hmacsha256,key="&key",message=name,return=hashname)
run;

Re: How to apply HMAC-SHA256 hash to a column of a data set ?

skalamat — Sat, 03 Feb 2018 07:50:26 GMT

%let dir1=%str(/new/dirct/loct/userLoc/logfiles/);
%let pgmname=%str(SchdDent_20170615);
%let date=&sysdate;
%let time=&systime;
%let ext1=%str(log);
%let break=%str(-);
%let final1=&dir1&pgmname&date&break&time..&ext1;
%put &final1;

proc printto log="&final1";
run; quit;

options nocenter ls=132 ps=32000 obs=50 compress=char;        

    libname in3 '/new/dirct/loct/userLoc/dss20170615/';
 
    
*Macro to read in one month of Sched Dent;        
%macro readSchdDent(fy);        
    libname inscen&fy. "/mainDb/publs/SchdDent/";    

    data force schdDent&fy.;    

    set inscen&fy..fy&fy.(keep = APPT_DT APPT_TIME SSN LASTNAME FIRSTNAME HIPAA_TAX1 HIPAA_TAX2 HIPAA_TAX3 HIPAA_TAX4 HIPAA_TAX5 SLOT_KEY FY FM);    
            if FM='01';
    run;    

    proc append data = schdDent&fy. base = in3.schdDent_step1 (compress=char) force;    
    run;    

    proc datasets;        
        delete schdDent&fy.;
    run;    

%mend readSchdDent;        

%readSchdDent(16);
%readSchdDent(17);

data _null_;
	file inc;   /* extrnal file Data Step used to write output from a PUT statement*/
	input @;  /* holds input rcd for the next INPUT statemnt within the same iteration of the DATA step */
	put _infile_;  /* writes the last input data rcd read from the current input file or from data lines after DATELINES statenment */
	cards4;

	filename cp temp;
	proc groovy classpath=cp;

	add sasjar="commons_codec" version="1.4.0.0_SAS_20100917120335"; *version is specific to SAS Installation and may differ from this;

	submit parseonly;
	 
	import javax.crypto.Mac
	import javax.crypto.spec.SecretKeySpec
	import org.apache.commons.codec.binary.Base64

	class SASHash { 
	    String base64hmacsha256(String key, String message) {
	        def mac = Mac.getInstance("HmacSHA256")
	        mac.init(new SecretKeySpec(key.getBytes(), "HmacSHA256"))
	        def hash = Base64.encodeBase64String(mac.doFinal(message.getBytes()))
	        return hash
	    }
	}
	 
	endsubmit;
	 
	quit;

	options set=classpath "%sysfunc(pathname(cp,f))";
;;;;
run;

* general purpose macro can be expanded to include additional methods;
%macro SASHash(action,key=key,message=message,return=hash,include=inc);
%goto &action;

%compile:
%include &include;
%return;

%declare:
if _n_=1 then declare javaobj SASHash('SASHash');
%return;

%base64hmacsha256:
SASHash.callStringMethod("&action",strip(&key),strip(&message),&return);
%return;
%mend;

%let key = sdfoij3242039sdflkj3r23w345v;
%SASHash(compile,include=inc)


data in3.schdDent_step1;
	modify in3.schdent_step1;

	%SASHash(declare)
	length hashname $ 128;
	%SASHash(base64hmacsha256,key="&key",message=ssn,return=ssn);
    
    /*ssn = put(md5(upcase(trim(coalesce(ssn,''))) || '^' || '703447'),$hex32.)*/

    lastname = put(md5(upcase(trim(coalesce(lastname,''))) || '^' || '703447'),$hex32.);
    firstname = put(md5(upcase(trim(coalesce(firstname,''))) || '^' || '703447'),$hex32.);

run;

filename out1 '/new/dirct/loct/userLoc/dss20170615/schdDent.txt';

proc export data=in3.schdDent_step1
   outfile=out1
   dbms=dlm
   replace;
   delimiter='|';
run;


/*compresses a file with encryption by calling pkzip (available in AIX) */

data _null_;
    call system ('pkzipc -add -passphrase=$@asdfsdf!@@# /new/dirct/loct/userLoc/dss20170615/schdDent.zip /new/dirct/loct/userLoc/dss20170615/schdDent.txt');
run;

The original code revised w/ yours incorporated is shown above. After changing the version we ended up adding your recommendation verbatim right after the top Macro except the excerpt below we placed in the Data Step where we are hashing the ssn and return the result back to it.

	%SASHash(declare)
	length hashname $ 128;
	%SASHash(base64hmacsha256,key="&key",message=ssn,return=ssn);

The code runs and returns hashed ssn values. We'll wait for any further remarks/comments/advice from your end before we close the thread.

We thank you very much for your assistance and the time you put to develop an answer to our Q.

S-

Re: How to apply HMAC-SHA256 hash to a column of a data set ?

skalamat — Sun, 04 Feb 2018 02:57:49 GMT

Correction on my own last comment about the Data Step code that should be as follows in order to execute hashing the ssn.

data in3.schdDent_step1;

	%SASHash(declare)
	length hashname $ 128;
        set in3.schdent_step1;
	%SASHash(base64hmacsha256,key="&key",message=ssn,return=ssn);
    
    /*ssn = put(md5(upcase(trim(coalesce(ssn,''))) || '^' || '703447'),$hex32.)*/

    lastname = put(md5(upcase(trim(coalesce(lastname,''))) || '^' || '703447'),$hex32.);
    firstname = put(md5(upcase(trim(coalesce(firstname,''))) || '^' || '703447'),$hex32.);

run;

Re: How to apply HMAC-SHA256 hash to a column of a data set ?

skalamat — Mon, 12 Feb 2018 18:58:46 GMT

%let dir1=%str(/new/dirct/loct/userLoc/logfiles/);
%let pgmname=%str(SchdDent_20170615);
%let date=&sysdate;
%let time=&systime;
%let ext1=%str(log);
%let break=%str(-);
%let final1=&dir1&pgmname&date&break&time..&ext1;
%put &final1;

%let key1 = %nrstr(kd94hf93k423kf44&pfkkdhi9sl3r4s00) ;

proc printto log="&final1";
run; quit;


* can be a physical file using;filename inc temp;
data _null_;
file inc;
input @;
put _infile_;
cards4;
filename cp temp;
proc groovy classpath=cp;

add sasjar="commons_codec" version="1.4.0.0_SAS_20100917120335"; *version is specific to SAS Installation and may differ from this;

submit parseonly;
 
import javax.crypto.Mac
import javax.crypto.spec.SecretKeySpec
import org.apache.commons.codec.binary.Base64

class SASHash { 
    String base64hmacsha256(String key, String message) {
        def mac = Mac.getInstance("HmacSHA256")
        mac.init(new SecretKeySpec(key.getBytes(), "HmacSHA256"))
        def hash = Base64.encodeBase64String(mac.doFinal(message.getBytes()))
        return hash
    }
}
 
endsubmit;
 
quit;

options set=classpath "%sysfunc(pathname(cp,f))";
;;;;
run;

* generic macro that can be expanded to include additional methods;
%macro SASHash(action,key=key,message=message,return=hash,include=inc);
%goto &action;

%compile:
%include &include;
%return;

%declare:
if _n_=1 then declare javaobj SASHash('SASHash');
%return;

%base64hmacsha256:
SASHash.callStringMethod("&action",strip(&key),strip(&message),&return);
%return;
%mend;

*key can be set per user's need;
%let key = sklnvhjsz34545456dfbzdaldmgd3;
%SASHash(compile,include=inc)


options nocenter ls=132 ps=32000 obs=50 compress=char;        

    libname in3 '/new/dirct/loct/userLoc/dss20170615/';
 
    
*Macro to read in one month of Sched Dent;        
%macro readSchdDent(fy);        
    libname inscen&fy. "/mainDb/publs/SchdDent/";    
	
	data SchdDent&fy.;	
		set inscen&fy..header; /*(keep = fldName)	*/
		/*if FM='01'  */
	run;

    proc append base = in3.SchdDent_step1 data = SchdDent&fy.  (compress=char);	
	run;    

	proc datasets;		
		delete SchdDent&fy.;
	run;

	data in3.SchdDent_step1;
                %SASHash(declare)
                length ssnhsh $ 128;

		set in3.SchdDent_step1 ;
                %SASHash(base64hmacsha256,key="&key",message=ssn,return=ssnhsh)
		

		/* Add more flds to be encrypted by copying/pasting the code above and then replace the fld name */
		/* ssn = put(md5(upcase(trim(coalesce(ssn,''))) || '^' || '703617'),$hex32.); */
	run;

	filename out1 "/new/dirct/loct/userLoc/dss20170615/SchdDent_FY&fy..txt";

	proc export data=in3.SchdDent_step1 
	   outfile=out1 
	   dbms=dlm
	   replace;
	  /* putnames=yes; */
	   delimiter='|';
	run;

	data in3.SchdDent_step1;
		set in3.SchdDent_step1 (obs=0);
	run;

	%let glbFY = &fy.;

	data _null_ ;
		fyx = "&glbFY";
		command = 'pkzipc -add -passphrase=$@gR!xdgdfSAg /new/dirct/loct/userLoc/dss20170615/SchdDent_FY'||fyx||'.zip /new/dirct/loct/userLoc/dss20170615/SchdDent_FY'||fyx||'.txt';
		call system (command);
	run;


%mend readSchdDent;        

data _null_;
  do i=14 to 17 by 1;
	command1 ='%nrstr(%readSchdDent('||strip(i)||'))';
	call execute (command1);
  end;
run;

The code above incorporates FriedEgg's excerpt successfully and it returns 4 txt files and their zipped counterparts populated with an ssn hashed with HMAC SHA256 for 4 Fiscal Yrs

Sharing just in case someone else has a similar wonder.

S-