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.
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.
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.