CODEWITHSUNDEEP
insertsas
DarkousPl
DarkousPl

Reputation: 87

SAS join (or insert) little table to big table

I have little problem. I have big table and few little table where little tables including part of fields from big table. How I can insert (or union) tables on the basis of if field is the same - set data, if little table not have field from big - set null/0 in big table.

Example:

data temp1;
infile DATALINES dsd missover;
input a b c d e f g;
CARDS;
1, 2, 3, 4,5,6
2, 3, , 5
3, 3
4,,3,2,3,
;
run;

data temp2;
infile DATALINES dsd missover;
input a c e g;
CARDS;
5, 2, 3, 4
6, 3, , 5
7, 3
;
run;

Is there an elegant method where if I insert temp2 to temp1 - missing fields in temp2 will set value of null in temp1?

Thank you for help!

Upvotes: 0

Views: 622

Answers (3)

Tom
Tom

Reputation: 51566

That is exactly what SAS does by default.

data want ; 
  set have1 have2;
run;

It will match the variables by name and any variables that do not exist (in either source) will have missing values.

For better performance when appending a small table to a large table you should use PROC APPEND instead of a data step to avoid having to make new copy of the large dataset. That is more like an "insert". The FORCE option will allow the dataset to be different. But since the new data is being added to the old dataset any extra variables that appear only in HAVE2 will just be ignored and their values will be lost.

proc append base=have1 data=have2 force ;
run;

If you really did have to generate an actual INSERT statement (perhaps you are actually trying to generate SQL code to run in a foreign database) you might want to compare the metadata of the two datasets and find the common variables.

proc contents data=have1 out=cont1 noprint; run;
proc contents data=have2 out=cont2 noprint; run;
proc sql noprint;
  select a.name into :varlist separated by ','
  from cont2 a 
  inner join cont1 b 
  on upcase(a.name) = upcase(b.name)
  ;
...
insert into have1 (&varlist) select &varlist from have2 ;

Upvotes: 1

Kiran
Kiran

Reputation: 3315

you should try proc append.that will be more efficient because you will not reading your big table again and again unlike in

 /*reads temp1 which is big table and temp2*/

 data temp3; 
 set temp1 temp2;
  run;

/* this does pretty much same as above code but will not read your big table and will be efficient*/

proc append base=temp1 data=temp2 force;
 run;

more on proc append in documentation http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n19kwc3onglzh2n1l2k4e39edv3x.htm

Upvotes: 1

Vojta F
Vojta F

Reputation: 564

It is not very clear to me what operation you intend to do but some initial thoughts are:

  1. To compare columns between two datasets (and check whether a value exists in one of them) it is good practice to use an outer join. You can do joins via MERGE clause in a datastep, or more elegantly use PROC SQL.
  2. However, using either approach you will have to specify which two rows in temp1 and temp2 shall be compared - you are typically joining on a column that is available in both tables.

To help us resolve your issue, could you possibly provide the correct output for your desired operation, if you perform it on temp1 and temp2? This would show what options you've explored and what needs to be fixed there.

Upvotes: 1

Related Questions