Hi,
I've been tasked with counting the number of formats assigned to about 300 datasets within a bunch of folders.
I've already got all of the libnames assigned and i have a table with the dictionary.columns information, which has the FORMAT variable, but it's filled with 12k records, and some are user defined formats and some are system formats (date fields, lengths, etc).
Is there an easy way to split these? Does SAS record a system formats flag somewhere?
Thanks
Depends on how you're storing them.
But take a look at sashelp.vformat and see if you can filter them out. SAS formats have the path field filled out, but the user defined formats may not. And the libname doesn't appear to be assigned for system formats either.
So user defined formats are possibly identified via:
data user_formats;
set sashelp.vformat;
where not missing(libname);
run;
For the sake of the post, let's assume i have multiple datasets and no catalog to go with. I just have datasets with formats assigned in them. And i want to be able to tell the difference between user defined and system defined. I found the vformat table and it looks like it's all system defined. There's no libname for any of them. When i ran the format that (I can't see now who it was since i don't have the entire post on the reply screen) the other commenter included and re-ran it, then it selected that one out.
But i don't have the proc format code. I'm not running the formats. They're already in the datasets. But it looks like they only get added to vformat during the session in which they're run.
If you don't have them defined SAS doesn't know they exist and can't use them. Usually if you try and open a dataset with user defined formats that you don't have declared somewhere then you'll get an error. Unless you've set the NOFMTERROR option.
If they are defined use CNTLOUT to dump the definitions to dataset.
Proc format cntlout=format_defns;
run;
It's hard to understand exactly what you're trying to do or achieve here. Your initial question was answered but it appears to not be your actual question. Please be more descriptive with what you're trying to do if you need further help. The answer above is a guess.
After running the following code, you will see customize format have FormatSource='C' .
proc format;
value myfmt
1='F'
2='M';
run;
proc sql;
create table format as
select * from dictionary.formats;
quit;
When i filter this dictionary table to just the libnames i want i get no results.
You get eventually no results because the format names assigned to variables don't exactly match the "base" format names - eg. $12. would be "base" format $.
Examine below working code which uses what @Ksharp proposed for identifying OOTB formats. This sample code will hopefully give you some guidance how you can achieve what you're after.
proc format;
value justATest
1='test'
;
run;
data test;
format a justATest1.;
a=1;
run;
/* formats in datasets in selected libraries */
proc sql;
create table DistinctFormatsInLibs as
select distinct
format,
upcase(substrn(format,1,findc(format,'$','ba'))) as Format_Name
from dictionary.columns
where libname in ('SASHELP','WORK') and not missing(format)
;
quit;
/* OOTB Formats */
proc sql;
create table OOTB_Formats as
select upcase(fmtname) as fmtname
from dictionary.formats
where fmttype='F' and source ne 'C'
order by fmtname
;
quit;
/* Report */
proc sql;
create table want as
select
l.format as Format_Applied,
l.Format_Name,
case
when(l.Format_Name='' or r.fmtname ne '') then '1'
else '0'
end
as OOTB_FMT_FLG
from
DistinctFormatsInLibs as L
left join
OOTB_Formats as R
on l.Format_Name=r.fmtname
;
quit;
It is very possible that the library the format is in does not match the library the data set using it is in. Some organizations have shared custom formats placed in a central library but the data using them could be scattered.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.