topic Re: Create a new dataset in SAS Procedures

Create a new dataset

GokulK — Tue, 14 Dec 2021 17:18:05 GMT

Hello All,

I have two datasets—Dataset Test_Sample and Full_Sample where the dataset Test_Sample is a subset of Full_Sample. I want to delete observations that I have in Test_Sample from Full_Sample. For instance, if my Test_Sample and Full_Sample look as follows:

Test_Sample

Firm Year Size

A 2011 8

A 2012 8.1

B 2011 7

Full_sample

Firm Year Size

A 2011 8

A 2012 8.1

B 2011 7

B 2012 7.2

C 2011 7.9

C 2012 8.1

The desired output is

Firm Year Size

B 2012 7.2

C 2011 7.9

C 2012 8.1

Can someone please share the code with me so that I can get the desired output?

Best regards,

Gokul

Re: Create a new dataset

Astounding — Tue, 14 Dec 2021 17:48:14 GMT

Here's the basic idea:

data desired;
   merge test_sample (in=delete_these) full_sample;
   by firm year;
   if delete_these then delete;
run;

There is some question about what should happen if the FIRM and YEAR match, but the SIZE is different. This program gets rid of those observations.

Re: Create a new dataset

GokulK — Tue, 14 Dec 2021 17:54:58 GMT

Thank you for the prompt reply. I don't want to match by SIZE. As long as the FIRM and YEAR of both the samples match, then I want to delete such observations from full_sample.

Best regards,

Gokul

Re: Create a new dataset

Reeza — Tue, 14 Dec 2021 18:08:36 GMT

If the values differ does it matter which one is kept?

Re: Create a new dataset

GokulK — Tue, 14 Dec 2021 18:17:37 GMT

Hello Reeza,

Full_sample observations = Test_sample observations + non test firm-year observations. Since Full_sample has more observations, I want to delete firm-year observations from full_sample that are similar to Test_sample. So I will have three unique samples--Full_sample, Test_sample, and NonTest_sample.

Best regards,

Gokul

Re: Create a new dataset

Ksharp — Wed, 15 Dec 2021 12:28:15 GMT

data test;
input Firm   $    Year     Size;
cards;
A            2011      8
A            2012      8.1
B            2011      7
;
 

data Full;
input Firm   $   Year    Size;
cards;
A            2011      8
A            2012      8.1
B            2011      7
B            2012      7.2
C            2011      7.9
C            2012      8.1
;

proc sql;
create table want as
select * from full
except
select * from test
;
quit;