Help using Base SAS procedures

creating a new data set from an old data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

creating a new data set from an old data

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.


Accepted Solutions
Solution
‎05-01-2012 08:05 PM
Super Contributor
Posts: 1,636

Re: creating a new data set from an old data

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


All Replies
Solution
‎05-01-2012 08:05 PM
Super Contributor
Posts: 1,636

Re: creating a new data set from an old data

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

Frequent Contributor
Posts: 96

Re: creating a new data set from an old data


Thank you Linlin for the code.

Super User
Posts: 10,023

Re: creating a new data set from an old data

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

Super Contributor
Posts: 349

Re: creating a new data set from an old data

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

Frequent Contributor
Posts: 96

Re: creating a new data set from an old data

Thank you Shivas for the code.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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