Help using Base SAS procedures

adding two dataset

Reply
Contributor
Posts: 28

adding two dataset

Hello

How can I add two variables from two dataset?
As the picture.

I am currently using the rename function and join dataset in one and adds variables. Seeking a less troublesome solution.





I have another question
How to change this command to create a dataset with the result of the command.


from: http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361224.htm

[First or second table, but not both]

There is no keyword in PROC SQL that returns unique rows from the first and second table, but not rows that occur in both. Here is one way you can simulate this operation:

(query1 except query2) union (query2 except query1)

This example shows how to use this operation.

proc sql;
 title 'A EXCLUSIVE UNION B';
 (select * from sql.a except select * from sql.b)
 union 
(select * from sql.b except select * from sql.a);


Thank you for your help.

Super Contributor
Posts: 578

Re: adding two dataset

For the first part, if you're joining by id, then its pretty straightforward depending on which table (if any) would be the "master" table.  If neither, then you might consider:

proc sql;

create table want as

select

     coalesce(t1,id, t2.id) as ID,

     coalesce(t1.a,0) + coalesce(t2.a,0) as a

from

     dataset1 t1

     full outer join dataset2 t2

          on t1.id=t2.id;

quit;

you will have some issues if id is not unique in either table.

For the second part (without testing):

proc sql;

create table want as

select t1.* from (

     (select * from sql.a except select * from sql.b)

     union

     (Select * from sql.b except select * from sql.a)

) t1;

quit;

Trusted Advisor
Posts: 1,228

Re: adding two dataset

proc sql;

create table three as

select first.id,sum(first.a,second.a) as a

from first inner join second

on first.id=second.id;

quit;

Ask a Question
Discussion stats
  • 2 replies
  • 191 views
  • 6 likes
  • 3 in conversation