topic Re: Merging tables based on dates and names in SAS Programming

Merging tables based on dates and names

Agent1592 — Tue, 07 Nov 2017 07:22:21 GMT

Hi:

I have two files. The first has the fdate_1 and conm and the second has the annual standard deviation on a specific date. I am trying to merge the two files using PROC SQL. How can I keep everything from the file fdate_1 but take the annual SD that matches that date in fdate_1. What is the best PROC SQL SAS Code? Do I need to transpose the data?

The Result table should have the following columns:

fdate_1 (from file FDATE_1), conm (FDATE_1), ANNUAL_SD (from file ANNUAL_SD). Both files have to be matched on company name and date.

Re: Merging tables based on dates and names

Agent1592 — Tue, 07 Nov 2017 03:17:20 GMT

The first have table is:

fdate_1	conm
3/7/2007	ASTRAZENECA PLC
3/12/2008	ASTRAZENECA PLC
3/10/2009	ASTRAZENECA PLC
3/29/2010	ASTRAZENECA PLC
3/21/2011	ASTRAZENECA PLC
3/31/2012	ASTRAZENECA PLC
3/27/2013	ASTRAZENECA PLC
3/22/2014	ASTRAZENECA PLC
3/11/2015	ASTRAZENECA PLC
3/10/2016	ASTRAZENECA PLC
3/8/2017	ASTRAZENECA PLC
4/8/2007	BARCLAYS PLC
2/24/2008	BARCLAYS PLC
2/17/2009	BARCLAYS PLC
3/26/2010	BARCLAYS PLC
4/1/2011	BARCLAYS PLC
3/15/2012	BARCLAYS PLC
3/19/2013	BARCLAYS PLC
3/8/2014	BARCLAYS PLC
3/4/2015	BARCLAYS PLC
3/3/2016	BARCLAYS PLC
2/25/2017	BARCLAYS PLC

The second have table is:

Name

ASTRAZENECA PLC

BARCLAYS PLC

10/22/2003

0.318770648

0.365655639

10/23/2003

0.319576766

0.365696105

10/24/2003

0.315151655

0.36529699

10/27/2003

0.315135441

0.365901666

10/28/2003

0.314288405

0.365839302

10/29/2003

0.314282227

0.3640955

10/30/2003

0.314280745

0.363681904

10/31/2003

0.31502193

0.363453814

11/3/2003

0.314916729

0.362960075

11/4/2003

0.314783127

0.362684286

11/5/2003

0.314656318

0.362129068

11/6/2003

0.314328055

0.359455301

11/7/2003

0.313814151

0.35967231

11/10/2003

0.314921273

0.358688956

11/11/2003

0.315702997

0.358445754

11/12/2003

0.315242266

0.358239571

11/13/2003

0.315456853

0.357913949

11/14/2003

0.316638767

0.358119589

11/17/2003

0.317464788

0.356989507

11/18/2003

0.317374269

0.354504357

The want table is:

fdate_1	conm	STD (taken from the second have table)
3/7/2007	ASTRAZENECA PLC
3/12/2008	ASTRAZENECA PLC
3/10/2009	ASTRAZENECA PLC
3/29/2010	ASTRAZENECA PLC
3/21/2011	ASTRAZENECA PLC
3/31/2012	ASTRAZENECA PLC
3/27/2013	ASTRAZENECA PLC
3/22/2014	ASTRAZENECA PLC
3/11/2015	ASTRAZENECA PLC
3/10/2016	ASTRAZENECA PLC
3/8/2017	ASTRAZENECA PLC
4/8/2007	BARCLAYS PLC
2/24/2008	BARCLAYS PLC
2/17/2009	BARCLAYS PLC
3/26/2010	BARCLAYS PLC
4/1/2011	BARCLAYS PLC
3/15/2012	BARCLAYS PLC
3/19/2013	BARCLAYS PLC
3/8/2014	BARCLAYS PLC
3/4/2015	BARCLAYS PLC
3/3/2016	BARCLAYS PLC
2/25/2017	BARCLAYS PLC
3/11/2007	BNP PARIBAS

Re: Merging tables based on dates and names

Agent1592 — Tue, 07 Nov 2017 04:49:28 GMT

The problem is not all the dates are matching so I was wondering if we can match the closest dates.

Re: Merging tables based on dates and names

Patrick — Tue, 07 Nov 2017 05:10:42 GMT

@Agent1592

If you search the communities here with keywords like join by closest date you'll find tracks as below:

https://communities.sas.com/t5/SAS-Data-Management/Merging-two-tables-by-choosing-the-CLOSEST-dates/td-p/191428

Re: Merging tables based on dates and names

Agent1592 — Tue, 07 Nov 2017 05:31:10 GMT

Thank you, yes I was able to merge by closest dates but my question is how to do the first part of the merge. So far I have to do it manually. The two tables are in different format.

Re: Merging tables based on dates and names

Kurt_Bremser — Tue, 07 Nov 2017 07:01:09 GMT

There are no SAS datasets in your post, only useless Excel files. Excel files know no column attributes and can never convey important dataset metadata.

Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your datasets to datastep code, and post that according to https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce.

Re: Merging tables based on dates and names

Agent1592 — Tue, 07 Nov 2017 07:18:37 GMT

I tried to attach SAS files earlier.

Re: Merging tables based on dates and names

Agent1592 — Tue, 07 Nov 2017 07:20:02 GMT

Yes I apologize, I included the SAS files. Had to ZIP the files first. The software would not let me upload SAS files directly.

Re: Merging tables based on dates and names

Kurt_Bremser — Tue, 07 Nov 2017 07:45:30 GMT

Please read my previous post again. The correct and optimal way to post example data is to convert it to a data step and post the resulting code here. Code can be copied/pasted easily (use the proper code posting windows, as described) and is not version- or encoding-specific.

SAS datasets might not be usable because of encoding differences (wlatin1 - UTF), code works.

Second, never rename your SAS dataset files to something with uppercase letters. SAS physical file names only contain lowercase letters, which is important on operating systems that are case sensitive (all UNIX variants).

Having said that, I used this code to expand your annual_sd dataset to include all dates, so it can now safely be merged with fdate_1:

proc transpose
  data=sascomm.annual_sd (rename=(name=fdate_1))
  out=annual_sd (rename=(_name_=conm col1=sd))
;
by fdate_1;
run;

proc sort data=annual_sd;
by conm fdate_1;
run;

data annual_sd_ext1;
set annual_sd;
by conm;
prevdate = lag(fdate_1);
prevsd = lag(sd);
enddate = fdate_1;
endsd = sd;
if not first.conm and fdate_1 ne prevdate + 1
then do;
  do fdate_1 = prevdate + 1 to enddate;
    if fdate_1 > (prevdate + enddate) / 2
    then sd = endsd;
    else sd = prevsd;
    output;
  end;
end;
fdate_1 = enddate;
sd = endsd;
output;
drop prevdate prevsd enddate endsd;
run;

Take look at the logic and see if it fits your needs.