topic Re: creating a new data set from an old data in SAS Procedures

creating a new data set from an old data

shalmali — Tue, 01 May 2012 23:41:59 GMT

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.

Re: creating a new data set from an old data

Linlin — Wed, 02 May 2012 00:05:44 GMT

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

Re: creating a new data set from an old data

Ksharp — Wed, 02 May 2012 05:06:40 GMT

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

Re: creating a new data set from an old data

shivas — Wed, 02 May 2012 06:38:49 GMT

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

Re: creating a new data set from an old data

shalmali — Wed, 02 May 2012 16:47:16 GMT

Thank you Linlin for the code.

Re: creating a new data set from an old data

shalmali — Wed, 02 May 2012 16:47:58 GMT

Thank you Shivas for the code.