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.
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
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
Thank you Linlin for the code.
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
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
Thank you Shivas for the code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.