PROC FORMAT QUERY

[Anuz]

Hi ,

I have set up a format to be applied to a column that can come with three values - EXL1 , EXL2 and EXL3

Proc Format;
Value $EXCLPOST 
'EXL1' = 'ACTIVE BLOCKS'
'EXL2' = 'INACTIVE BLOCKS'
'EXL3' = 'INVESTIGATION BLOCKS'
Run;

When this format was applied against the dataset for that column, it works just fine

DATA EXL_DAILY_DATA;
input EXL_FLAG $200.;
datalines;
EXL1
EXL2
;
run;
 
 
DATA EXL_DAILY_DATA;
input EXL_FLAG $200.;
datalines;
EXL1
EXL2
;
run;
 
DATA EXL_DAILY_DATA;
set EXL_DAILY_DATA;
format EXL_FLAG $EXCLPOST.;
run;

Now I have started receiving data where the column could have multiple values in any combination separated by a pipe. For Eg:

DATA EXL_DAILY_DATA;
input EXL_FLAG $200.;
datalines;
EXL1|EXL2
EXL2|EXL3
;
run;

Now when the format is applied it will not work and will only display the pipe delimited values that came in the input.

How can i modify the format in a way that it applies the format to the individual values and come out as

ACTIVE BLOCKS | INACTIVE BLOCKS
INACTIVE BLOCKS | INVESTIGATION BLOCKS

[PaigeMiller]

Separate the pipe-delimited fields into individual variables, one for each "word". Then apply the format to each variable.

[ballardw]

Personally I would modify the program that reads the data to separate the values into multiple variables.

Question: did anyone tell you that this change to multiple values was going to occur? If not you have what can become a serious issue with data reliability in general and depending on how you read your files you may have other issues related to changing data structures.

You really do not want multiple values in a SAS data set variable. Really, it causes problems with analysis and reporting and can make graphing nearly impossible to be meaningful. For one thing what do you do when you have a mix of values like

EXL1|EXL2

EXL2|EXL1

If those are supposed to be considered as the same that will be very hard to deal with and require additional manipulation of the data to make it so. And the approach gets worse as soon as someone adds a third (or 4th or 5th) value.

[Anuz]

Thank you for your reply.
Yes. been told it was changing into the format i put into the example.

It is only ever going to be one or the combination of the three values.
No analysis is to be done on this data. It is just display what it means.

From what you saying it seems the best option might be a tranwrd if applying the format across is not going to be feasible

[SASKiwi]

If you just want to display your data as is then just add to your PROC FORMAT like so:

Proc Format;
Value $EXCLPOST 
'EXL1' = 'ACTIVE BLOCKS'
'EXL2' = 'INACTIVE BLOCKS'
'EXL3' = 'INVESTIGATION BLOCKS'
'EXL1|EXL2' = 'ACTIVE BLOCKS | INACTIVE BLOCKS'
'EXL2|EXL3' = 'INACTIVE BLOCKS | INVESTIGATION BLOCKS'
;
Run;

[Kurt_Bremser]

Which other values in our input would demand that these values be combined in a single observation?

I'd rather read these double values into multiple observations.

[Patrick]

Character formats only work on the full value (string) and not on substrings. 

If you want to continue using a format then one option would be to create a function and then use this function in the format as done in below sample code.

data have;
  infile datalines truncover;
  input exl_flag $200.;
  datalines;
EXL1|EXL2
EXL2|EXL3
EXL1
EXL2
EXL2|EXL9
;

proc fcmp outlib=work.funcs.myfuncs;
  function EXCLPOST(instr $) $;
    length outstr $60;
    outstr=tranwrd(instr,'EXL1','ACTIVE BLOCKS');;
    outstr=tranwrd(outstr,'EXL2','INACTIVE BLOCKS');;
    outstr=tranwrd(outstr,'EXL3','INVESTIGATION BLOCKS');;
    return(outstr);
  endsub;
run;

options cmplib=work.funcs;

proc format;
  value $exclpost(default=60)
    other=[exclpost()]
  ;
run;

proc print data=have;
  format exl_flag $exclpost.;
run;