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.
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.
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.
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.
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.
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.
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.