Dear Experts,
Would there be any way or function to apply the user-defined format to the variable if exists/present in the dataset? for example something like below.
Data test1;
set test;
if var1 exists then do;
format var1 format1;
end;
if var2 exists then do;
format var2 format2;
end;
if var3 exists then do;
format var3 format3;
end;
run;
Thank you!
If you only want to assign formats, without other changes to the data, use proc datasets.
Set up a control table:
data control;
input varname :$32. fmtname :$32.;
datalines;
var1 format1.
var2 format2.
var3 format3.
;
run;
Check which variables are present:
proc sql;
create table correction as
select a.varname, a.fmtname
from control a, dictionary.columns b
where upcast(a.varname) = upcast(b.name) and
b.libname = "WORK" and b.memname = "TEST";
quit;
Dynamically create the code for proc datasets:
data _null_;
set correction end=eof;
if _n_ = 1 then call execute("
proc datasets library=WORK noprint;
modify TEST;
format
");
call execute(catx(" ",varname,fmtname, ""));
if eof then call execute("
;
quit;
");
run;
The FORMAT statement is not an executable statement so you would have to use macro code to write this statement if the variable exists or not write it at all if not.
Thanks for replying.
Would you please give me a little example?
If you only want to assign formats, without other changes to the data, use proc datasets.
Set up a control table:
data control;
input varname :$32. fmtname :$32.;
datalines;
var1 format1.
var2 format2.
var3 format3.
;
run;
Check which variables are present:
proc sql;
create table correction as
select a.varname, a.fmtname
from control a, dictionary.columns b
where upcast(a.varname) = upcast(b.name) and
b.libname = "WORK" and b.memname = "TEST";
quit;
Dynamically create the code for proc datasets:
data _null_;
set correction end=eof;
if _n_ = 1 then call execute("
proc datasets library=WORK noprint;
modify TEST;
format
");
call execute(catx(" ",varname,fmtname, ""));
if eof then call execute("
;
quit;
");
run;
Thank you so much, it works perfectly fine.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.