topic Re: User defined format from reference SQL table in SAS Programming

User defined format from reference SQL table

Babloo — Mon, 02 Sep 2019 15:11:56 GMT

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?

Re: User defined format from reference SQL table

Tom — Mon, 02 Sep 2019 15:27:41 GMT

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

Re: User defined format from reference SQL table

Babloo — Mon, 02 Sep 2019 16:06:35 GMT

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

Re: User defined format from reference SQL table

Tom — Mon, 02 Sep 2019 17:34:25 GMT

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.

Re: User defined format from reference SQL table

Reeza — Tue, 03 Sep 2019 02:52:14 GMT

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.

Re: User defined format from reference SQL table

Kurt_Bremser — Tue, 03 Sep 2019 05:29:56 GMT

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

Re: User defined format from reference SQL table

Babloo — Tue, 03 Sep 2019 05:37:35 GMT

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

Re: User defined format from reference SQL table

Kurt_Bremser — Tue, 03 Sep 2019 06:45:07 GMT

@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

Re: User defined format from reference SQL table

Reeza — Tue, 03 Sep 2019 13:11:56 GMT

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