Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Masking Social Security Number in Excel
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Masking Social Security Number in Excel
 Login/Join
 
Member
posted
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
 
Posts: 21 | Location: Minneapolis, MN | Registered: November 17, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
What do you get when you simply print out "SSNMASK"?


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: Minneapolis, MN | Registered: November 17, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6124 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Masking Social Security Number in Excel

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.