creating a new data set from an old data

[shalmali]

Hello Everyone,

I have two datasets Y1 and Y2. Both the data has a list of firms. Some of the firms in Y2 are also in Y1. I want to create a new dataset Y3 from Y2 that does not includes any firms present in Y1.

data y1;
input firm $;
cards;
2000     A   

2000     B    
2001     A    
2002     A    
2002     C   

2002     D   

;
RUN;

data y2;
input year firm $ ;
cards;
2000    a    

2000    c     
2000     o   
2000     p   

2001     q   

2001     r   

2002     s   

2002     t   
2002     c   
2002      v  
2002     w   
2002     x    
;
RUN;

Desired output (data y3)

year firm   
2000     o   
2000     p    
2001     q   
2001     r    
2002     s    
2002     t   

2002      v   
2002     w    
2002     x   

Thank you.

[Linlin · Accepted solution]

data y1;

input year :firm $;

cards;

2000     A

2000     B 

2001     A 

2002     A 

2002     C

2002     D

;

RUN;

data y2;

input year firm $ ;

cards;

2000    a

2000    c  

2000     o

2000     p

2001     q

2001     r

2002     s

2002     t

2002     c

2002      v

2002     w

2002     x 

;

RUN;

proc sql;

create table y3 as select * from y2

   where upcase(y2.firm) not in (select firm from y1);

quit;

proc print;run;

Message was edited by: Linlin

[shalmali]

Thank you Linlin for the code.

[Ksharp]

OR.

data y1;
input year firm $;
cards;
2000     A
2000     B 
2001     A 
2002     A 
2002     C
2002     D
;
RUN;

data y2;
input year firm $ ;
cards;
2000    a
2000    c  
2000     o
2000     p
2001     q
2001     r
2002     s
2002     t
2002     c
2002      v
2002     w
2002     x 
;
RUN;

proc sql;
create table y3 as select * from y2
   where upcase(y2.firm) ne  all(select firm from y1);
quit;

Ksharp

[shivas]

Hi,

Try this..

data y1;

input year :firm $;

firm=lowcase(firm);

cards;

2000     A

2000     B

2001     A

2002     A

2002     C

2002     D

;

RUN;

data y2;

input year firm $ ;

cards;

2000    a

2000    c 

2000     o

2000     p

2001     q

2001     r

2002     s

2002     t

2002     c

2002      v

2002     w

2002     x

;

RUN;

proc sort data=y1;by firm;run;

proc sort data=y2;by firm;run;

data want;

merge y1(in=a) y2(in=b);

by firm;

if b and not a ;

run;

Thanks,

Shiva

[shalmali]

Thank you Shivas for the code.