topic Re: where statement is not working after using a proc format. in SAS Enterprise Guide

where statement is not working after using a proc format.

alepage — Mon, 27 Oct 2025 21:55:53 GMT

Hello,

I have use a user define proc format below. But when come the time to trace observations where flob in ('PONAUO','PONPRO') it is not working.

How to solve that issue.

Also, if substr(flob,1,2) ='PO' and broker_group_ind eq 'IP' then substr(flob,1,2) ='PT'

how to make this mapping conversion?

proc format;
value $FLOB

'COMMAUTO'='CONAUO'
'OMEGAUTO'='OMNAUO'
'PROGAUTO'='PRNAUO'
'TRUCAUTO'='TRNAUO'
'COMMPROP'='CONPRO'
'E&OPROP'='EONLIA'
'D&OPROP'='DONLIA'
'OMEGPROP'='OMNPRO'
'PROGPROP'='PRNPRO'
'NICHPROP'='NINPRO'
'MARIPROP'='CONMAR'
'FIDEPROP'='CONFID'
'CRIME'='CONFID'
'HAILFARM'='CFNHAI'
'PERSAUTO'='PONAUO'
'PERSMOTO'='PONMOO'
'PERSTOYS'='PONTOO'
'PERSPROP'='PONPRO'
'COMMSURD'='CONSUD'
'COMMSURE'='CONSUR'
'COMMSURO'='CONSUO'
'COMMFARM'='CFNPRO'
'COMMTECH'='TENEOL'
'COMMENTE'='ENNLIA'
;

run;

DATA INFORCE_SEP2025 (rename=(LOB=FLOB));
SET INFORCE_SEP2025;
format lob $FLOB.;
where newren eq 'N';
RUN;

Re: where statement is not working after using a proc format.

SASKiwi — Mon, 27 Oct 2025 22:08:38 GMT

Applying a format to a variable doesn't change the value stored, only how it is displayed. If you want to use a WHERE clause on a formatted value you have to use the PUT function:

where put(lob, $flob.) in ('PONAUO','PONPRO');

Re: where statement is not working after using a proc format.

Tom — Mon, 27 Oct 2025 22:43:32 GMT

Note: It is dangerous to replace a dataset when making changes, especially when you are not sure the code is going to work. For example if you ran your posted data step twice the second run would fail.

178  DATA INFORCE_SEP2025 (rename=(LOB=FLOB));
179    SET INFORCE_SEP2025;
180    format lob $FLOB.;
181    where newren eq 'N';
182  RUN;

NOTE: There were 0 observations read from the data set WORK.INFORCE_SEP2025.
      WHERE newren='N';
NOTE: The data set WORK.INFORCE_SEP2025 has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


183
184  DATA INFORCE_SEP2025 (rename=(LOB=FLOB));
185    SET INFORCE_SEP2025;
186    format lob $FLOB.;
187    where newren eq 'N';
188  RUN;

NOTE: Variable lob is uninitialized.
WARNING: Variable LOB cannot be renamed to FLOB because FLOB already exists.
NOTE: There were 0 observations read from the data set WORK.INFORCE_SEP2025.
      WHERE newren='N';
NOTE: The data set WORK.INFORCE_SEP2025 has 0 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

If you want to use the format to CHANGE THE VALUES then perhaps you want something like this instead?

DATA INFORCE_SEP2025_modifed;
  SET INFORCE_SEP2025;
  where newren eq 'N';
  FLOB= put(lob,$FLOB.);
  drop lob;
RUN;

Re: where statement is not working after using a proc format.

quickbluefish — Tue, 28 Oct 2025 01:22:38 GMT

Re: your 2nd question about altering the substring, you should be able to do that exactly how you've written it:

if substr(flob,1,2) ='PO' and broker_group_ind eq 'IP' then substr(flob,1,2) ='PT';

The substr function is unusual in that it can be used on the left side of the equals sign. Not sure if that was your question.

I think if you're running everything in batch / from the command line, then overwriting temp datasets is fine and actually preferable in terms of memory usage and reducing clutter and opportunities for mistakes. But in EG / SAS Studio or other GUI, then yes, it can get you in trouble.

Re: where statement is not working after using a proc format.

Ksharp — Tue, 28 Oct 2025 03:27:38 GMT

Or try this one :

if strip(vvalue(lob)) in ('PONAUO','PONPRO');