Creating one table from two

Reply
Regular Contributor
Posts: 194

Creating one table from two

How do you create one table from two?

Table Name: Dogs

ID  Name       Address              Type

1   Sinclair     50 Haven Dr.        Dog

2   Cathy       23 Appian Way     Dog

5   Demitri     30 Ione Ln            Dog 

Table Name:  Cats

ID   Name      Address              Type

1    Sinclair    50 Haven Dr.         Cat

2    Cathy      23 Appian Way      Cat

3    Dione      123 High Tower      Cat

Results Table:

ID  Name       Address            NewType

1   Sinclair      50 Haven Dr.     Dog, Cat

2   Cathy        23 Appian Way  Dog, Cat

3  Dione         123 Hightower    Dog

5  Demitri        30 Ione Ln        Dog

I"ve tried this but I only get one column named 'Type'.

data merged;   

     merge Dogs Cats

          by ID;

run;     

    

Any suggestions?

Thank you!

Trusted Advisor
Posts: 1,137

Re: Creating one table from two

Hi,

Please try the below code

data dogs;

    input ID  Name $     Address & $20.  Type $;

cards;

1   Sinclair     50 Haven Dr.        Dog

2   Cathy       23 Appian Way        Dog

5   Demitri     30 Ione Ln            Dog

;

run;

data cats;

    input ID  Name $     Address & $20.  Type $;

cards;

1    Sinclair    50 Haven Dr.      Cat

2    Cathy      23 Appian Way      Cat

3    Dione      123 High Tower     Cat

;

run;

proc sort data=dogs;

    by id;

run;

proc sort data=cats;

    by id;

run;

data dogs_cats(drop=type type2);

    merge dogs cats(rename=(type=type2));

    by id;

    new_type=catx(',',type,type2);

run;

Thanks,

Jagadish

Thanks,
Jag
Super Contributor
Posts: 543

Re: Creating one table from two

Hi.

One thing I would suggest is to rename the variable "type" in both data sets as type_temp1 and type_temp2;

You can do this in your merge;

data merged;  

     merge Dogs(rename = (type = type_dogs)) Cats(rename = (type = type_cats));

          by ID;

     final_type = type_dog || " " || type_cats;*or you can use a couple of if statements;

     *this should give you some idea?;

run;    

Best of luck!

Anca

Super Contributor
Posts: 578

Re: Creating one table from two

also in proc sql;

proc sql;

create table want as

select

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

     ,coalesce(t1.name, t2.name) as name

     ,coalesce(t1.address,t2.address) as address

     ,catx(',',t1.type, t2.type) as type

from

     dogs t1

     full outer join cats t2

          on t1.id=t2.id;

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 199 views
  • 2 likes
  • 4 in conversation