Applying a format to only part of a comma delimited observation.

[jimbobob]

Morning Fellow SASers,

I have a field that is a concatenation of row exceptions into one observation separated by a comma example:

OBS	EXCPS	Format Applied
A	GHI, RR2P, YTZ, Z1G2P	Paid, Not Paid, Closed, Weak
B	A4H, MNL, PJF6	New, Renew, Extension
C	D334, FTR, XL1	Docs, Delinquent, Rate
D	THT, Z1G2P	THT, Weak

I can create a format with every CURRENT concatenated observation. But when a new EXCPS code comes in that hasn't been defined in the format, I still want to apply the format to part of the observation. But right now when I apply the format, OBS D, shows "THT, Z1G2P". The EXCPS field can have anywhere from 1 to up to 10 codes in the field, The observation itself is always concatenated in a Alpha-Numeric order.

Is it possible to apply a format to only part of a Observation?

[Reeza]

You cannot apply a format to part of an observation. You'll need to split them (look into SCAN()) and then concatenate them back. Within the format definition do you have an 'OTHER' option? I usually code OTHER='MISSING' or OTHER='CHECKME' so that it's clear when somethings gone wrong.

[ballardw]

---

@jimbobob wrote:

Morning Fellow SASers,

I have a field that is a concatenation of row exceptions into one observation separated by a comma example:

---

Question: did you concatenate the rows?

If so, the approach would be to use a format that addresses each single value instead of groups and use the formatted value when concatenating.

If the value is coming to you already concatenated the split it apart and apply a single valued format to rebuild a new string.

Something along:

proc format library=work;
value $status (default=8)
'GHI'    = 'Paid'
'RR2P'   = 'Not Paid'
'YTZ'    = 'Closed'
'Z1G2P'  = 'Weak'
other    = 'UNKNOWN'
;

data example;
   str1='GHI, RR2P, YTZ, Z1G2P, ABC';
   length str2 $ 100 temp1 temp2 $ 8;
   do i=1 to countw(str1);
      temp1  = scan(str1,i);
      temp2 = put(temp1,$status.);
      if temp2= 'UNKNOWN' then put "WARNING: Unknown code of: " temp1;
      str2=catx(', ',str2,temp2);
   end;
   drop temp1 temp2;
run;
 

Note the test of the value and a note in the log about a new value found. When you see one of those you would be able to add the value into the format with a formatted value and rerun to "fix" the issue.