04-16-2018 04:11 PM
I am trying to write a code for a data set that will always be changing. In my code, there is a macro where you put in the province code, and then a raw data file is imported and checked for errors. I want the program to flag it as an error, if there is an ABC code that is not an option for that particular province. For example, if the raw data file is say AB and the ABC code is not in 'A" or "KK" than I want that to be flagged as an error. So basically, what counts as an error will change based on the province code. Or if for example the "ABC Code" is R, and it is SK, that is no error, but if it is AB that is an error. And this table is always changing. But in essence, I want it to be an error, only if there is not an "ABC Code" for the specific province being checked.
It looks something like this:
04-16-2018 04:19 PM
if province_code='AB' and abc_code not in ('A','KK') then flag=1; else if province_code='SK' and abc_code not in ( ... ) then flag=1; /* You have to fill in the abc_codes for SK */
04-17-2018 08:24 AM
Hello! thanks for the reply I was originally thinking I would do something like that but the problem is that the table is actually huge and constantly changing, so I need to somehow get around writing all of the 'ABC codes'
04-16-2018 04:26 PM
A user defined format is a great idea here.
You can make one for each province, with OTHER/MISSING flagged as needed, and then any that don't meet your list will become ERROR that you can flag or modify as needed.
You can create formats from a data set so this can be entirely dynamic as your lists changes as needed.
Lets assume you have a macro variable PROV that's set to AB, and you then have a format called AB_valid_fmt, you can use PUTC to check the values.
check = putc(ABC, "&PROV_valid_fmt."); if check = "ERROR" then ...
04-17-2018 02:53 AM
Create a table with the valid pairings, and do a join/merge with that table. If abc_code is not missing and there's no match, you have an error.
That way you get intelligent code (does not need to be changed) out of intelligent data.
Alternatively, you can create a format out of that control dataset (make a compound value out of province and abc_code), and apply that to a compound of province and abc_code in your main dataset. Then you don't need the join/merge.