topic How to find the format definition in SAS DI? in SAS Data Management

How to find the format definition in SAS DI?

Babloo — Fri, 09 Mar 2018 09:48:12 GMT

There are a formats ($va_leadactie_grp., $va_leadactie_subgrp.) as below which has been used in the DI job under user written code transformation and now I want to know how the formats has been defined.

va_groep = put(groep,$va_leadactie_grp.) ; 
va_subgroep = put(subgroep,$va_leadactie_subgrp.) ;

I see that the following line has also been added. I looked into the library "it_rci_s" and it has multiple datasets but I couldn't see the word "va_leadactie_grp and va_leadactie_subgrp" any where in the datasets. Appreciate if someone one you guide me to see how the formats has been defined.

options fmtsearch=(it_rci_s);

Re: How to find the format definition in SAS DI?

LinusH — Fri, 09 Mar 2018 10:14:13 GMT

Formats are stored in catalogue entries, not in data sets.

Even if use of formats is a powerful SAS tool, they don't really match with metadata driven ETL approach, since generation of formats can not be tracked in SAS metadata.

So I tend to use formats only locally in jobs when there can be a substantional benefit compared with other techniques (look-up, join etc). In this manner, it's feasible to find your format definitions.

But in our current situation, try to find the catalogue that holds your formats, and perform a

PROC FORMAT CNTLOUT=work.yourformatdataset...

This will output a dataset that holds specifications for all format entries.

Re: How to find the format definition in SAS DI?

Babloo — Fri, 09 Mar 2018 10:40:50 GMT

Providing the original code which use the user defined formats.

data actieproducten ; 
		set actieproducten ; 
		length GROEP $2. SUBGROEP $4. VA_ACTIEPRODUCT $30. VA_GROEP VA_SUBGROEP $20.; 
		va_actieproduct = put(actieproduct,$va_makprd.) ; 
		va_groep = put(groep,$va_leadactie_grp.) ; 
		va_subgroep = put(subgroep,$va_leadactie_subgrp.) ; 
	run ;

Inorder to find the catalogue which holds my formats, I should execute the code as follows?

proc format CNTLOUT=work.actieproducten;

Re: How to find the format definition in SAS DI?

LinusH — Fri, 09 Mar 2018 11:07:14 GMT

To be precise, the formats is not defined in that code snippet, they are referred to.

Well, my code excerpt was just a limited example of the CNTLOUT option.

You need to supply the catalogue name as well.

Look up the PROC FORMAT in documentation.

Re: How to find the format definition in SAS DI?

ballardw — Fri, 09 Mar 2018 20:54:39 GMT

@Babloo wrote:

Inorder to find the catalogue which holds my formats, I should execute the code as follows?

You likely need to find where all of your formats are located as they could be in any library in your FMTSEARCH library list

proc sql;
   create table myfmts as
   select fmtname,libname, memname
   from dictionary.formats
   where fmtname in ('$VA_MAKPRD' '$VA_LEADACTIE_SUBGRP')
   ;
quit;

Will create a data set with the library and catalog (memname) for the formats. The fmtname is stored in all capital letters and DOES NOT use the period at the end. Place all of your desired to find formats in the search box, or assume that they are all in the same catalog (I wouldn't). Note if the catalog name is other than FORMATS you need to specify the name in the Proc Format code.

Strongly suggest saving the cntlout data set to a permanent library that will be maintained.