(Using user defined format in SAS di format tab) and (rename/drop column after work is done)

[ANIRBAN2]

1)How can I use User defined format which i have defined in the precode  but still not available in format drop-down in mapping tab.So how can i use this user defined format ..

plzz help

2)How to drop/rename a column from a target table. I dont want to drop in mapping coz i am using it.

I want to drop after if have used its value in some other column..

like we use in data step rename/drop but i want in SAS di. 

plz help....

[LinusH]

1) You just click in the cell and write. Unfortenately I don't think that you can make DI Studio UI be aware of user defined formats.

2) Not sure what you mean here. Mappings are for the target. If your columns is still in the source table you can still use it for other target column mappings? Or what do you mean by that you are using it?

[ANIRBAN2]

1) As i have already define the format value in the pre code its shown like this when i click and write the format i defined

2)As we  do in data step

Eg:-

data x(drop=make);

set sashelp.cars;

Run;

We drop the "make" variable after using it in the table x;

same way how can i drop? do i need to write post code to drop the varible in DI ?/there are some table options to drop from the target table??

[LinusH]

1) Formats must always be specified using a period at the end:

abc.

2) Please be more specific where you ancounter this problem. In what transformaiton?

Usually DI Studio defines the output table, and the appnd (maps) input data to it. So it usually does not drop the column from the source stament (SET or FROM).

[Patrick]

In the end SAS DIS just generates SAS code. So with your approach of using a user defined format created in the pre-code that you then use in the column definition of the target table (and as Linus wrote the format name must be in the form "format name"<w>.<d> but ALWAYS needs a dot) you could have the following problem:

IF in your pre-code you define a format that gets compiled into work and you then assign this user defined format to a variable in a permanent table then when using this table in another DIS job, you'll get an error because the format in Work doesn't exist there but the physical table will have a column definition which expects this format to exist.

If you really want to apply custom formats to permanent tables then I'd suggest that you create a separate DIS job (may-be for adhoc execution only) that compiles the macros into a permanent library catalog available to the whole SAS application. This needs to be a pre-assigned library defined in SAS metadata and you need also to add the libref to option FMTSEARCH in your application context.

I believe there is a OOTB permanent library already available. Run...

proc options option=fmtsearch;
run;

...this will tell you which libraries are already part of the format search path.

If using a OOTB library or a new project specific one is a design decision. If it was me then I'd go for a project specific library especially if this is not the only application running in the environment.

[ANIRBAN2]

Firstly I will says Thanks for explaining such a way...

My problem solved temporarily as u said..

If you really want to apply custom formats to permanent tables then I'd suggest that you create a separate DIS job (may-be for adhoc execution only) that compiles the macros into a permanent library catalog available to the whole SAS application. This needs to be a pre-assigned library defined in SAS metadata and you need also to add the libref to option FMTSEARCH in your application context.

I believe there is a OOTB permanent library already available. Run...

proc options option=fmtsearch;
run;

...this will tell you which libraries are already part of the format search path.

Can u please tell me what does adhoc means and how can I define the user defined format permanently as u said in DI?

What does OOTB means??

R  u telling  to create a job that contains the user defined format and call that in the precode of ever job?I dont think so??

How can i create permamently  and save in metadeta? if you tell please...

[Patrick]

OOTB: Out of the Box

If you execute a Proc Format then the format definition gets compiled into a SAS catalog within a SAS library.

When you use a format anywhere then SAS will search for this format in all locations defined via option FMTLIB.

If you don't define the library in the Proc Format statement then the format gets compiled into a catalog in WORK.

From the docu here:

So what I suggested was to explicitly define the library in the Proc Format statement using a libref of a pre-assigned permanent library. This way you can compile the macro (execute Proc Format) in one job but the compiled format will then also be available to other jobs (i.e. the one where you want to use the table that has a variable with this format attached).

And now because this format becomes relevant for more than one DIS job I wouldn't "hide" the format generation code in some pre-code but have this in its own user written node in the job or especially if you create more than one format in more than one place eventually have such "infrastructure" code in a separate DIS job. 

IF the formats are created from a table that changes regularly then you need to execute the job as part of your regular job flow, if they are scripted and only change as part of BAU change then you can also decide to not run this job every single time but to treat it as a maintenance adhoc job only to be run if the format definition gets changed as part of BAU (BAU: Business as usual).