Focal Point Community,

This community has provided me a lot of great ideas through the years, and I have yet another challenge. I'm hoping someone will have an amazing idea.

The short version of my team's challenge is this:

• Convert masked values which appear in carets (^) from an alphanumeric field to zeroes, nulls, whatever the case may be.
  
  
• Convert all values to numeric format.
  
  
• Take the resulting file and graph it minus the nulls, zeroes, etc.
  
  

We are not able to manipulate the master file. We need to have all the entire data set present in order for our custom masking procedure to work properly.

By way of an example from the CAR file, here is some code:

 TABLE FILE CAR
SUM
     CAR.BODY.SALES
BY  CAR.COMP.CAR
ACROSS LOWEST CAR.BODY.BODYTYPE
WHERE CAR.COMP.CAR NE 'JENSEN' OR 'PEUGEOT'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END 

If you look at the SEDAN column using the provided code, it has two carets.

For argument's sake, let's say those numbers were 7 and 4. Our masking procedure will mask both numbers because 7<4, and we mask anything <5; however, if we mask only one number, we mask the next smallest number. It means no one can do the math to figure out the masked values exactly.

They might know the combination of those two values is 11, but they don't know if the individual values are 6 and 5, 8 and 3, or 7 and 4.

Does anyone have any ideas on the most effective approach for this challenge? I really want to solve it soon.

I might note one suggestion made to me recently is to use -include and place my masked value file in my graph fex, do the conversions and then graph from there.

This message has been edited. Last edited by: FP Mod Chuck,

Are you trying to write a procedure that creates the masked values or are the values already masked? I've read your problem description several times and admittedly am having a hard time understanding what you're trying to accomplish.

Thank you for your reply.

I apologize for any confusion.

We have a procedure that we use to mask our values. We're wanting to graph results once everything is masked but not include the masked values.

I can post the code in about an hour or two, but if you look at the CAR file example I posted last week, we'd never include in the graph those values marked with a caret.

Does that make sense?

I ran your example and I see no caret in the output.

Well, let's try this again. I've taking a different approach to my challenge, and I am posting some sample code again, this time from the sample file Finance along with some samples of the results and what I need.

Here are my objectives:

• If Amount LE 1000 THEN turn it into a zero.
  
• If a column contains a zero as a result of #1, then turn the minimum result that is left in the column to a zero.
  

Use the resulting table to create the graph. The graph code will include a WHERE statement to remove any zeroes.

Here is the sample code without the minimum component.

 
DEFINE FILE FINANCE
AMOUNT/I4 = IF AMOUNT LE 1000 THEN '0' ELSE AMOUNT;
END

TABLE FILE FINANCE
SUM
     FINANCE.TOP.AMOUNT
ACROSS FINANCE.TOP.YEAR
BY  FINANCE.TOP.ACCOUNT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END 

Here are the results of the Finance file without any masking.

YEAR 1982 1983 
ACCOUNT   
1000 1,294,611 1,430,903 
1010 213,225 249,504 
2000 5,639 818 
4000 4,200 4,938 
5000 23,758 28,052 
6000 10,206 15,945 
6500 45,643 35,158 
6600 12,909 16,099 
6900 1,743 1,264 
7000 17,459 30,294 
  

Here are the results after I have completed step one of the two-step masking.

 YEAR 1982 1983 
ACCOUNT   
1000 1294611 1430903 
1010 213225 249504 
2000 5639 0 
4000 4200 4938 
5000 23758 28052 
6000 10206 15945 
6500 45643 35158 
6600 12909 16099 
6900 1743 1264 
7000 17459 30294 
 

For 1983, I need to turn 1,264 into a zero as it's the next minimum value. I can then eliminate zeroes from the graph part of the procedure using a WHERE statement.

Also, if 1983 already had two values turned to a zero, I would not need the minimum value turned to a zero.

Does that help explain things a little better?

Would something like this work?

 
TABLE FILE FINANCE
SUM COMPUTE NEW_AMOUNT/D12=IF AMOUNT LE 1000 THEN 0 ELSE AMOUNT;
BY YEAR
RANKED BY TOTAL LOWEST AMOUNT
BY ACCOUNT
ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE HOLD1
SUM AMOUNT
    COMPUTE ADJ_AMOUNT/D12=IF LAST NEW_AMOUNT EQ 0 AND RANK EQ 2 THEN 0 ELSE NEW_AMOUNT;
BY YEAR
BY RANK
BY ACCOUNT
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,$
ENDSTYLE
END
-RUN
-EXIT
  

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

Thank you so much! This works pretty well overall for us.

I am wondering if you have any suggestions on how to handle missing values. I'll read up on that again later as it's been awhile, but below is a dataset as it should appear on the final hold table.

 Red-30
Blue-7
Green-Missing
Yellow-Missing
Orange-Missing
Pink-Missing
Purple-191 

So what happens in that case is that none of the missing values show up, and that's correct; however, the value of "7" for blue does not show up either.

Other than that, the procedure works very well, and I thank you for your generosity in sharing the code.