topic Re: matching two data sets by one year lag using intnx function in SAS Programming

matching two data sets by one year lag using intnx function

Ziba — Thu, 31 Jan 2019 22:26:05 GMT

Dear All,

I have used the following code to merge two data sets,

proc sql;
create table own.want
as select a.*, b.*
from own.sub_9110 as a left join  own.dir_own as b
on a.cusip=b.cusip
and fy between intnx('year', yearfiled, -2, "sameday") and yearfiled;
quit;

I want to merge these two datasets by cusip (cusips are already in the same format) and pick up values from own.dir_own data( datasetb) which its fy(year variable in b) is exactly 2 years prior to the yearfield (year variable in a ).

in a, I have thecompany list with cusip and yearfield(like 2004, 2005,...not in month and day format) and in b I have all the director ownership for each company from 1992 till 2018, cusip and fy. So, this means that in b dataset I have more than one director ownership for each company;

Lets say company A in the data set a looks like:

name yearfield cusip .....

A 2004 12345678

company A in the data set b looks like:

name fy cusip dir_own ....

A 1992 12345678 45

A 1993 12345678 12

A 2002 12345678 20

A 2004 12345678 44

the final merged data set should look like this :

name cusip yearfiled fy dir_own

A 12345678 2004 2002 20

so it picked the director ownership which is 2 years prior to the yearfield;

but the code that i used grabbed all the observation, the final merged data looked like this :

name cusip yearfiled fy dir_own

A 12345678 2004 1992 45

A 12345678 2004 1993 12

A 12345678 2004 2002 20

A 12345678 2004 2004 44

I am not sure how to correct the code, hope you guys can help,

thanks in advance.

Ziba

Re: matching two data sets by one year lag using intnx function

mkeintz — Thu, 31 Jan 2019 22:52:45 GMT

First, your variables FY and YEARFILED do not appear to be sas date variables. Instead they look like 4-digit numbers (unless they are really sas date values, formatted as YEAR4.). So the INTNX function is useless. If will interpret the value 2004 as 27JUN1965 (2004 days after 01jan1960). And subtracting 2 years, via the INTNX function, will generate 27JUN1963. So you could replace

and fy between intnx('year', yearfiled, -2, "sameday") and yearfiled;

with

and fy between yearfiled -2  and yearfiled;

Re: matching two data sets by one year lag using intnx function

novinosrin — Thu, 31 Jan 2019 23:01:52 GMT

No clue what your objective is, but looking only at your data and the want

data b;
input name   $       fy          cusip                dir_own ;
cards ;
A                 1992      12345678            45
A                 1993      12345678            12
A                 2002     12345678            20
A                2004     12345678            44
;

data a;
input name    $   yearfield     cusip  ;
cards;
A                2004          12345678
;

proc sql;
create table want
as select a.*, fy,dir_own
from a as a left join  b as b
on a.cusip=b.cusip and  fy=yearfield-2;
quit;

The above will get you 2002 fy resulting in just 1 record

The below

on a.cusip=b.cusip and fy between yearfield -2  and yearfield;

will result in 2002 ,2004 with 2 records in result

Re: matching two data sets by one year lag using intnx function

ballardw — Thu, 31 Jan 2019 22:58:39 GMT

You should show what the actual desired output should look like. You show what it shouldn't be but that doesn't say what should.

Re: matching two data sets by one year lag using intnx function

Ziba — Thu, 31 Jan 2019 23:02:35 GMT

what if I formatted the date variables in Year4. format and should I still have to replace?

Re: matching two data sets by one year lag using intnx function

Ziba — Thu, 31 Jan 2019 23:05:34 GMT

hey

sorry I didnt say it clearly

the is actual desired output:

name cusip yearfiled fy dir_own

A 12345678 2004 2002 20

Re: matching two data sets by one year lag using intnx function

Ziba — Thu, 31 Jan 2019 23:06:15 GMT

hey ,

sorry I didnt say it clearly

the is the actual desired output:

name cusip yearfiled fy dir_own

A 12345678 2004 2002 20

Re: matching two data sets by one year lag using intnx function

mkeintz — Fri, 01 Feb 2019 00:55:11 GMT

Which did you do?

Re: matching two data sets by one year lag using intnx function

Ziba — Fri, 01 Feb 2019 01:19:01 GMT

I used the following code as I cant format the year variable in data set a. (they all became 1965 after I used year4. format ). this code generated record that are one year prior to yearfiled date.

proc sql;
create table own.own_patent_list
as select a.*, b.*
from own.sub_9110 as a left join  own.dir_own as b
on a.cusip=b.cusip
and year=yearfiled-1;
quit;

but I still have one question: I also tried the second code you provided (year between yearfiled-3 and yearfiled-1;) to replace year=yearfiled-1, and then this time I have maximum of three records for each company. what if I want to pick the first nonzero value within those three rows of records for that company, and delete the rest?

I cant use if statement in pro sql, so any ideas ??

Thanks