topic Re: Inserting data into a table that has more columns in SAS Programming

Inserting data into a table that has more columns

Myurathan — Tue, 13 Nov 2018 09:46:50 GMT

Hi Helpers,

I have a table with 200 columns. I would push this table's data into a master table that has 250 columns. Column names are same. When I push the data I want unavailable columns to get a NULL value. I am using following code to push my data to a table that has same columns.

Proc SQL;
  Insert Into one.tbl_one Select * From tble_two;
Quit;

Please help.

Thanks in advance.

Re: Inserting data into a table that has more columns

RW9 — Tue, 13 Nov 2018 09:56:44 GMT

Some starter questions.

Why have a dataset with 200+ variables, sounds like bad modelling in either SAS or Database.

Why use SQL, unless your connecting to a database, it would be easier to set two datasets in datastep.

Re: Inserting data into a table that has more columns

Myurathan — Tue, 13 Nov 2018 10:00:21 GMT

It has 200+ variables as many units use the table for various purposes. It is pushing the data to a SQL database. There might be a better way to do it which I am not aware as I am a beginner. Thanks, @RW9.

Re: Inserting data into a table that has more columns

singhsahab — Tue, 13 Nov 2018 10:03:38 GMT

Hello ,

In your case proc SQL insert will not work. You can use PROC APPEND procedure with FORCE options.

proc append base=tble_one data=tble_two force;
run;

Re: Inserting data into a table that has more columns

RW9 — Tue, 13 Nov 2018 10:59:31 GMT

Well, if you continue with the bad model, and continue to use SQL, then your only option is to put the variables you want to insert into the code:

proc sql;
  insert into one.tbl_one (var1,var2,var3...)
  select * From tble_two;
quit;

SQL wasn't designed to be used with lots of variables, so its a chore to work with if you have a bad model. Vice versa, SAS datastep is easier to work with more variables.