topic Re: How to judge a variable has custom format or not? in SAS Programming

How to judge a variable has custom format or not?

whymath — Tue, 14 Jan 2025 08:49:00 GMT

I want to develop a macro, named %var_hascustfmt, to judge a variable has custom format or not. It is designed to return 1 or 0, for variable has custom format or not.

This is the source code:

%macro var_hascustfmt(data,var);
%local did did2 result;
%let did=%sysfunc(open(&data.));
%if &did.^=0 %then %do;
  %let fmtname=%sysfunc(varfmt(&did.,%sysfunc(varnum(&did.,&var.))));
  %let did=%sysfunc(close(&did.));

  %let result=0;
  %if &fmtname.^= %then %do;
    %let did2=%sysfunc(open(sashelp.vcformat(where=(trim(fmtname)||'.'="&fmtname."))));
    %if %sysfunc(attrn(&did2.,nlobsf))^=0 %then %let result=1;
    %let did2=%sysfunc(close(&did2.));
  %end;

%end;
&result.
%mend;

Test data:

proc format;
  value $gender
  'F'='Female'
  'M'='Male'
  ;
run;

data have;
  subject=1;
  gender='F';
  birthdate='01jan2000'd;
  weight=59;
  format subject z3. gender $gender. birthdate e8601da.;
run;

Test code and result:

%put %var_hascustfmt(have,subject);
0
%put %var_hascustfmt(have,gender);
1
%put %var_hascustfmt(have,birthdate);
0
%put %var_hascustfmt(have,weight);
0

So far, it works good. As you can see, the macro return value by query name of variable's format in sashelp.vcformat, which is a view of dictionary.formats.

My question is:

1. Is this way always reliable? What does sashelp.vcformt stores in official definition?

2. Is there any other implements? Access to dictionary table is not always fast enough.

Re: How to judge a variable has custom format or not?

ChrisNZ — Tue, 14 Jan 2025 10:57:35 GMT

A few issues with your code, the main one being that it only detects custom formats that have been defined.

This works:

%macro var_hascustfmt(ds, var);
  %local dsid result;
  %let dsid=%sysfunc(open(&ds.));
  %if &dsid. %then %do;
    %let fmtname=%sysfunc(varfmt(&dsid.,%sysfunc(varnum(&dsid.,&var.))));                            %* Get format name from source table;
    %let fmtname=%sysfunc(prxchange(s/[\d\.]*$//, 1, &fmtname.));                                    %* Remove format length & final dot ;
    %let dsid   =%sysfunc(close(&dsid.));                                                            %* Close source table               ;
    %if %length(&fmtname.) %then %do;                                                                %* Var format found, vet it         ;
      %let dsid  =%sysfunc(open(SASHELP.VFORMAT(where=(FMTNAME="&fmtname" & SOURCE in ('U','B'))))); %* See if format is standard        ;
      %let result=%sysfunc(ifc(%sysfunc(attrn(&dsid.,nlobsf)), 0, 1));                               %* Not standard => set flag to 1    ;
      %let dsid  =%sysfunc(close(&dsid.));                                                           %* Close format table               ;
    %end;                                                                                            %* End of 'var format found'        ;
    %else %let result=0;                                                                             %* No format => set flag to 0       ;
  %end;                                                                                              %* End of format search             ;      
  %else %let result=-1;                                                                              %* No data set => set flag to -1    ;    
&result.
%mend;

proc format;
  value $gender
  'F'='Female'
  'M'='Male'
  ;
run;

data HAVE;
  SUBJECT  =1;
  GENDER   ='F';
  BIRTHDATE='01jan2000'd;
  WEIGHT   =59;
  format SUBJECT z13.2 GENDER $gender. BIRTHDATE e8601da. AGE sssss.;
run;

%put %var_hascustfmt(HAVE,SUBJECT);    %* z13.2       ;
%put %var_hascustfmt(HAVE,GENDER);     %* Custom      ;
%put %var_hascustfmt(HAVE,BIRTHDATE);  %* e8601da.    ;
%put %var_hascustfmt(HAVE,WEIGHT);     %* Unformatted ;
%put %var_hascustfmt(HAVE,AGE);        %* Undefined   ;
%put %var_hascustfmt(HAVEXX,WEIGHT);   %* No data set ;

This should be further improved by checking that the variable exists.

Re: How to judge a variable has custom format or not?

ChrisNZ — Tue, 14 Jan 2025 10:59:30 GMT

If you want to speed up the query, copy the table (only 2 columns and only U and B values) the first time you run the macro, and then query the copy.

Re: How to judge a variable has custom format or not?

whymath — Wed, 15 Jan 2025 02:34:42 GMT

Thank you, what does U and B means?

Re: How to judge a variable has custom format or not?

Tom — Wed, 15 Jan 2025 04:20:51 GMT

Wouldn't it be easier to just generate some SAS code to do that work instead of all of those %SYSFUNC() calls?

What value does it add to jump through so many hoops make it simulate a function?

Plus if you generate SAS code instead you can do things like use the FORMAT variable generated by PROC CONTENTS so you don't have to work to strip off the width and period and decimal place specifications.

Re: How to judge a variable has custom format or not?

ChrisNZ — Wed, 15 Jan 2025 08:10:17 GMT

U and B just show how the format is packaged internally in SAS. B stands for Base iirc. See here for a related discussion https://support.sas.com/resources/papers/proceedings17/SAS0209-2017.pdf

Re: How to judge a variable has custom format or not?

ChrisNZ — Wed, 15 Jan 2025 08:16:36 GMT

SAS code shows in the log and restrict how the function can be used. There's nothing wrong with using %sysfunc, especially for such a tiny block of code.

Re: How to judge a variable has custom format or not?

whymath — Wed, 15 Jan 2025 08:49:43 GMT

Yes, you are right, I have several peices of code to achieve the similiar thing now:

*Method 1;
data _null_;
  if 0 then set have(keep=subject);
  _vformatn_=vformatn(subject);

  set sashelp.vcformat;
  if fmtname=_vformatn_ then call symputx('customformatexist',1);
run;

*Method 2;
proc contents data=have out=tab1(where=(name='subject')) noprint;
run;

proc sql noprint;
  select 1 into :customformatexist trimmed from tab1 
  where format in (select fmtname from dictionary.formats where source='C');
quit;

They all work.

Thank you for inspiring me, I decide to use the proc content way in another macro.

Re: How to judge a variable has custom format or not?

ChrisNZ — Wed, 15 Jan 2025 10:26:21 GMT

1. You're devaluing your function by using SAS code.

For example, you can no longer run

data _null_;
  VAR='WEIGHT';
  if resolve('%var_hascustfmt(HAVEXX,'||VAR||')') = '-1' then putlog 'No data set!';
run;

There are ways around this, but why not use what works?

2. Again, your code will not work if the custom formats have not been defined.

Test against SAS format names, not against custom format names.

Re: How to judge a variable has custom format or not?

Tom — Wed, 15 Jan 2025 16:58:35 GMT

Another hint for code like that last one.

proc sql noprint;
  select 1 into :customformatexist trimmed from tab1 
  where format in (select fmtname from dictionary.formats where source='C');
quit;

Make sure to initialize the macro variable since when the no observations meet the WHERE condition the macro variable is not generated.

Since macro variables are just text just use '1' instead of 1 in the query. Then you won't need the TRIMMED keyword since nothing needs trimming.

proc sql noprint;
%let customformatexist = 0;
select '1' into :customformatexist 
  from tab1 
  where format in (select fmtname from dictionary.formats where source='C')
;
quit;

Re: How to judge a variable has custom format or not?

whymath — Thu, 16 Jan 2025 01:53:23 GMT

Many thanks to your second hint, will use it in real work.
For the first one, I actually use functionality of %var_hascustfmt() in another big macro, so parameters 'data' and 'var' are validated.

Re: How to judge a variable has custom format or not?

whymath — Thu, 16 Jan 2025 01:55:26 GMT

Nice trick.