BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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);

 

 

4 REPLIES 4
LinusH
Tourmaline | Level 20

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. 

 

Data never sleeps
Babloo
Rhodochrosite | Level 12

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;
LinusH
Tourmaline | Level 20

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.

Data never sleeps
ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 782 views
  • 3 likes
  • 3 in conversation