topic Re: Telling the diff between system and user formats in SAS Programming

Telling the diff between system and user formats

MeganE — Tue, 11 Oct 2016 19:02:43 GMT

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

Re: Telling the diff between system and user formats

Reeza — Tue, 11 Oct 2016 19:40:10 GMT

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;

Re: Telling the diff between system and user formats

Ksharp — Wed, 12 Oct 2016 02:56:36 GMT

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;

Re: Telling the diff between system and user formats

MeganE — Wed, 12 Oct 2016 11:50:54 GMT

When i filter this dictionary table to just the libnames i want i get no results.

Re: Telling the diff between system and user formats

MeganE — Wed, 12 Oct 2016 11:54:59 GMT

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.

Re: Telling the diff between system and user formats

Reeza — Wed, 12 Oct 2016 12:17:46 GMT

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.

Re: Telling the diff between system and user formats

Patrick — Wed, 12 Oct 2016 13:33:39 GMT

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;

Re: Telling the diff between system and user formats

ballardw — Wed, 12 Oct 2016 14:53:43 GMT

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.