topic Re: Insert default row into tables in SAS Data Management

Insert default row into tables

amorts — Fri, 03 Nov 2017 10:15:49 GMT

I have quite a basic requirement to insert a record into a table and based on a hard coded sql statement using SAS DI.

Initially I thought I could use a insert row transform but this requires a source table. So what is the best way of doing this?

Re: Insert default row into tables

RW9 — Fri, 03 Nov 2017 10:22:23 GMT

Perhaps providing some information, examples about your question would be helpful.

At a guess, SQL needs at least one source record to insert a record in a dataset, so just choose one of the sashlpe datasets which you can identify the one record from and use that with a where - or alternatively, create a dataset with one row and use that. Without any other info not sure what to say.

Re: Insert default row into tables

amorts — Fri, 03 Nov 2017 11:08:11 GMT

If it helps I'll be more specific. I have a bunch of empty dimension table following an initial deployment. I need to insert a default unknown record in each of them. As an example:
-1, 'Unknown'
This will be hardcoded as a sql statement so there is no source table.
Let me know what you need I'll provide additional information.

Re: Insert default row into tables

RW9 — Fri, 03 Nov 2017 11:14:29 GMT

Its advisable to show specific examples, I can't see your process. I also don't know what "This will be hardcoded as a sql statement so there is no source table." means. At a guess, and I show a specific example to show what I have taken from your text:

data have;
  length a b $50;
  output;
  output;
run;

proc sql;
  update have
  set a="1-Unknown",
      b="1-Unknown";
quit;

Re: Insert default row into tables

Patrick — Fri, 03 Nov 2017 23:11:56 GMT

@amorts

Assuming the target table exists and is never empty: Just use the target table also as source table, select only 1 record (if it's a SAS table then use obs=1) and then use the expressions to set the columns to the values of your choosing before loading into the target table.

You can use the APPEND transformation for all of this.

Re: Insert default row into tables

amorts — Sat, 04 Nov 2017 07:24:11 GMT

Thanks both for your help! I will give that a go.