2

I have an existing ETL solution built-in SAS Data Integration, where one of the columns in initially set to have all null values. I want to populate that column with actual data. The original column in that table was set to receive numeric values with specific format and in format. After I am changing the code (that is the easy part), I notice that the column doesn't accept character values (I did not get an error, I just noticed the column still having all NULL values). Can anyone help ?

B--rian
  • 5,578
  • 10
  • 38
  • 89
Eyal Marom
  • 281
  • 4
  • 18
  • Why did you define it as numeric if you wanted to load it with character strings? Change the step that creates the table, then the step that loads data into it will work. – Tom Jun 21 '20 at 14:31
  • The table was created a longtime ago. Could you point me to how it is done ? I know how to change the code to what I want. How do I change to column format in the table ? – Eyal Marom Jun 21 '20 at 15:24
  • So do you want to encode your strings into numbers? Or create a new table that has that variable defined as character? You cannot just change the type of a variable, you need to make a new variable. Explain more the whole process to get a better answer. – Tom Jun 22 '20 at 02:29

1 Answers1

2

So you have a table that is defined in Data Integration studio (1) and created by running the job (2) a long time ago with a numeric column. Let us call that table THE_TABLE that field the_field and the job, The_Job, that loads data into THE_TABLE

You must be aware of the fundamental difference

  1. defining a THE_TABLE in DI studio, which creates a description of the table in meta data
  2. creating THE_TABLE by running The_Job, which creates a file in a folder with data

If The_Job really creates THE_TABLE from scratch each time (which is typical for ETL jobs), it is sufficient to do change THE_TABLE and The_Job in DI studio. Your edits will only change the meta data, but the next time you run The_job, THE_TABLE wil be created with the the right structure.

However, if The_Job updates THE_TABLE or appends to it, your edits will not change the structure of THE_TABLE and your job will not be fit for the structure of the file THE_TABLE like it still exists in the folder, so you must convert THE_TABLE before running The_Job.

This can be done with a simple program like

data THE_TABLE;
   set THE_TABLE (drop=the_field);            /* forget about the numeric field */
   attrib the_field length=$200 format=$200.; /* and create the character field */
run;

The correct attrib statement might well be in the code generated for The_Job somewhere.

Mind that in a typical setup with a development, test and production environment, you will need that program once in each environment.

Dirk Horsten
  • 3,753
  • 4
  • 20
  • 37