Hello mentors,
I have a question regarding check for empty formats and would appreciate your guidance
I have datasets that contain ‘id’ values only and I have created separate formats from those id datasets.
There are 6 datasets: table 1-6, they all contain a column called id (char) such that:
-same id value could be present in multiple tables
-the tables are being created by some other process, so they could be blank.
%macro crt_frmt(ds);
proc sort data=&ds. nodupkey;
by id;
run;
data &ds._format;
set &ds.;
fmtname = "$&ds._format";
type = 'c';
start = strip(id);
end = strip(id);
label = 'YES';
run;
proc format cntlin= &ds._format;
run;
%mend;
%crt_frmt(table1);
%crt_frmt(table2);
%crt_frmt(table3);
%crt_frmt(table4);
%crt_frmt(table5);
%crt_frmt(table6);
I then use these table formats to flag observations and in the later part of the code delete and do other things my master dataset using these formats:
data master;
set master;
if put(id,$table1_format.) = "YES" then flag=1;
if put(id,$table2_format.) = "YES" then flag=2;
if put(id,$table3_format.) = "YES" then flag=3;
if put(id,$table4_format.) = "YES" then flag=4;
if put(id,$table5_format.) = "YES" then flag=5;
if put(id,$table6_format.) = "YES" then flag=6;
run;
proc freq data=master;
table flag /list missing;
run;
The issue is, there are times when some tables are empty, so that creates an empty format. When I create flags in the ‘master’ datastep, it gives me an error:
"ERROR: The format $table1_format was not found or could not be loaded"
Which is understandable since it was an empty dataset. But how do I conditionally run the put statements in my master dataset so that they run only when formats are present?
I tried adding this within the macro:
%let dsid=%sysfunc(open(&ds.));
%let &ds._format_nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
%put &ds._format_nobs= &ds._format_nobs;
Such that in the master dataset I could do:
data master;
set master;
if table1_format_nobs>0 then do;
if put(id,$table1_format.) = "YES" then flag=1;
end;
:
:
But this obviously isn’t right, since table1_format_nobs is a macro variable and cannot be referenced this way.
Appreciate the help and time!
Thank you.
Instead of conditionally executing data step statements perhaps set a specific format for the formatted value.
%macro crt_frmt(ds); %if %sysfunc(exist(&ds.)) %then %do; proc sort data=&ds. nodupkey; by id; run; data &ds._format; set &ds.; fmtname = "$&ds._format"; type = 'c'; start = strip(id); end = strip(id); label = 'YES'; run; proc format cntlin= &ds._format; run; %end; %else %do; /* code here to make sure you have a $&ds._format that will work as needed*/ /* maybe */ proc format; value "$&ds._format" other = "NO" ; run; %end; %mend;
You didn't provide much example of how that format is used so …
Your data step that is creating the dataset to feed to PROC FORMAT is wrong.
You have the SET statement as the first statement. That means that if the input is empty the data step stops before it can ever output anything.
So add an OTHER choice to your format definition. Then empty datasets do not cause the format to not be defined.
Try something like this:
data &ds._format;
retain fmtname "$&ds._format";
retain type 'c';
retain hlo ' ';
retain label 'YES';
if eof then do ;
label='NO';
hlo='O';
output;
end;
set &ds. (rename=(id=start)) end=eof;
output;
run;
To create a format from a dataset use the CNTLIN= option on the PROC FORMAT statement.
proc format cntlin=&ds._format;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.