BookmarkSubscribeRSS Feed
amorts
Calcite | Level 5

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?

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

?

 

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.

 

amorts
Calcite | Level 5
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Patrick
Opal | Level 21

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

amorts
Calcite | Level 5
Thanks both for your help! I will give that a go.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 975 views
  • 0 likes
  • 3 in conversation