BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shalmali
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5


Thank you Linlin for the code.

Ksharp
Super User

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
Pyrite | Level 9

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
Calcite | Level 5

Thank you Shivas for the code.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1054 views
  • 6 likes
  • 4 in conversation