User defined format from reference SQL table

[Babloo]

There is a variable called Entity_Id in SAS dataset and for that variable I have to apply the format where the format is located in the SQL table. I don't want to create the SAS dataset of that SQL table.

How can I apply the format from SQL to SAS?

[Tom]

---

@Babloo wrote:
There is a variable called Entity_Id in SAS dataset and for that variable I have to apply the format where the format is located in the SQL table. I don't want to create the SAS dataset of that SQL table.

How can I apply the format from SQL to SAS?

---

You will either need to explain better and/or provide a concrete example.

What do you mean by "apply the format" to ENTITY_ID?  Do you mean use a format to convert the values of ENTITY_ID into some other text?

What is an "SQL table"? How is that different than a SAS dataset?  Are you querying some external database?  If so how are you going to use information from that external database without reading it?

[Babloo]

Yes, I want to apply the format to convert the values of Entity_ID to some
other text.

Yes, I want to query the external database.

Below example is the data which I have in external database (sql server) as
a table.Right now I have the variable in SAS with old_entity_id and I want
to convert it into new_entity_id

E.g.

New_Entity_id Old_entity_id
1234 12345
6789 67890

[Tom]

You could use the values from that external table to define a format.  Assuming you have made a libref MYSQLLIB that points to your database/schema and the mapping data is in a table named MY_MAPPING_TABLE.  Also assuming the ENTITY_ID is a character string (why would you use a number for something you would never want to do arithmetic with?) then code might look like this:

proc sql;
create table fmt as
select distinct
      '$new_entity_id' as fmtname
     , old_entity_id as start
     , new_entity_id as label
from mysqllib.my_mapping_table
order by 1,2
;
quit;
proc format cntlin=fmt; run;
data want ;
  set have ;
  new_entity_id = put(entity_id,$new_entity_id.);
run;

But you cannot define a format in SAS without actually reading the data that defines it into SAS.  

If the source table is large and the SAS table that has the values that need mapping is small it might be fastest to just pull the values you need from the external database.  

[Reeza]

I don't want to create the SAS dataset of that SQL table.

You'll have to unfortunately, or at minimum create the lookup table. 

[Babloo]

May I know how can I create the look up table?

[Kurt_Bremser]

---

@Babloo wrote:
May I know how can I create the look up table?

---

Once again, Maxim 1: Read the Documentation.

Input Control Data Set in PROC FORMAT.

You need 5 variables:

• fmtname - name of the format
• type - 'C' for character, 'N' for numeric
• start - the value to be formatted
• label - the formatted value
• hlo - set to 'O' in an observation that covers all other values

[Reeza]

---

@Babloo wrote:
May I know how can I create the look up table?

---

Same way you reference any other table, Tom's code is correct, assuming we've interpreted your question correctly. 

[Kurt_Bremser]

---

@Babloo wrote:
I don't want to create the SAS dataset of that SQL table.

---

Then use a view. But since cntlin datasets are quite small by nature (only 5 columns needed), there's no need to avoid building the input data as a dataset.