Focal Point
[CLOSED] Masking Social Security Number in Excel

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/754109625

September 13, 2010, 10:31 AM
JimBowe
[CLOSED] Masking Social Security Number in Excel
I am need of masking SSN's at my company. I am able to mask the first 5 digits in PDF and HTML but not ing EXL2K.
I tried this code using EXL2k(EXCEL is showing first 4 digits of SSN instead of last 4 digits) :
DEFINE FILE NBTVAUDT ADD
SSNMASK/A4 = EDIT(FEDTAXID, '$$$$$9999');

SSNMASK2/A9 = IF '&MASK_IND' EQ 'Y' THEN 'XXXXX' | 'SSNMASK' ELSE IF FEDTAXID IS MISSING THEN '' ELSE FEDTAXID; END

TABLE FILE NBTVAUDT
PRINT SSNMASK2
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT,COLUMN=EVENTDESC,WRAP=OFF,$
TYPE=REPORT,COLUMN=PLANNME,WRAP=OFF,$
TYPE=REPORT,COLUMN=BEFORE,WRAP=3.5,$
TYPE=REPORT,COLUMN=AFTER,WRAP=3.5,$
ENDSTYLE
ON TABLE PCHOLD AS OUTFILE FORMAT EXL2K END

Even if I use:
SSNMASK/A4 = IF '&MASK_IND' EQ 'Y' THEN EDIT(FEDTAXID, '9999') IF FEDTAXID IS MISSING THEN '' ELSE FEDTAXID;

I sitll get XXXXX1234 (Where the SSN = 123456789)

This message has been edited. Last edited by: Kerry,


Jim
Prod: WebFOCUS 7.6.5 CGI - Self Service - AIX 5.2 - IBM DB2 V9
Env 3 Local: DevStudio 7.6.5 Servlet - Self Service - MS Windows XP SP2 - Output: HTML, Excel 2000 and PDF
September 13, 2010, 10:45 AM
Glenda
What do you get when you simply print out "SSNMASK"?


Glenda

In FOCUS Since 1990
Production 8.2 Windows
September 13, 2010, 10:52 AM
Glenda
Your code appears to be correct (see Car sample). What is the format of "FEDTAXID"?

-SET &SSN      = 123456789;
-SET &MASK_IND = Y;
DEFINE FILE CAR
FEDTAXID/A9 = '&SSN.EVAL';
SSNMASK/A4 = EDIT(FEDTAXID, '$$$$$9999');

SSNMASK2/A9 = IF '&MASK_IND' EQ 'Y' THEN 'XXXXX' | 'SSNMASK' ELSE IF FEDTAXID IS MISSING THEN '' ELSE FEDTAXID; END
END

TABLE FILE CAR
PRINT SSNMASK
MODEL NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K
END



Glenda

In FOCUS Since 1990
Production 8.2 Windows
September 13, 2010, 11:53 AM
JimBowe
My output for SSNMASK is:
XXXXX1234 (Where it should be XXXXX6789)

The Format for FEDTAXID:
FIELD=FEDTAXID ,FEDTAXID,
A9 ,A9 ,MISSING=OFF,$


Jim
Prod: WebFOCUS 7.6.5 CGI - Self Service - AIX 5.2 - IBM DB2 V9
Env 3 Local: DevStudio 7.6.5 Servlet - Self Service - MS Windows XP SP2 - Output: HTML, Excel 2000 and PDF
September 13, 2010, 12:11 PM
Glenda
Print out SSNMASK & SSNMASK2

DEFINE FILE NBTVAUDT ADD
SSNMASK/A4 = EDIT(FEDTAXID, '$$$$$9999');
SSNMASK2/A9 = IF '&MASK_IND' EQ 'Y' THEN 'XXXXX' | 'SSNMASK' ELSE IF FEDTAXID IS MISSING THEN '' ELSE FEDTAXID; 
END
-*
TABLE FILE NBTVAUDT
PRINT 
      SSNMASK
      SSNMASK2
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT,COLUMN=EVENTDESC,WRAP=OFF,$
TYPE=REPORT,COLUMN=PLANNME,WRAP=OFF,$
TYPE=REPORT,COLUMN=BEFORE,WRAP=3.5,$
TYPE=REPORT,COLUMN=AFTER,WRAP=3.5,$
ENDSTYLE
ON TABLE PCHOLD AS OUTFILE FORMAT EXL2K END




Glenda

In FOCUS Since 1990
Production 8.2 Windows
September 13, 2010, 05:34 PM
Waz
quote:
'SSNMASK'


Try removing the single quotes from around SSNMASK

Also remove the END from the definition of SSNMASK2, after the semicolon.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

September 14, 2010, 03:48 AM
GamP
And apart from all this, I feel that the type of output should not make any difference at all. If the output is to html it should produce the same values as when the output is excel.
Try it like this:
DEFINE FILE NBTVAUDT
SSNMASK2/A9 = IF '&MASK_IND' EQ 'Y' THEN EDIT(FEDTAXID, 'XXXXX$$$$$9999') ELSE
              IF FEDTAXID IS MISSING THEN '' ELSE FEDTAXID;
END

TABLE FILE NBTVAUDT
PRINT FEDTASKID SSNMASK2
ON TABLE PCHOLD AS OUTFILE FORMAT EXL2K
END



GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988