DATA Step, Macro, Functions and more

Merging tables based on dates and names

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Merging tables based on dates and names

[ Edited ]

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.


 

Attachment

Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 7,854

Re: Merging tables based on dates and names

Posted in reply to Agent1592

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Contributor
Posts: 36

Re: Merging tables based on dates and names

Posted in reply to Agent1592

The first have table is:

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

 

The second have table is:

Name
ASTRAZENECA PLCBARCLAYS PLC
10/22/20030.3187706480.365655639
10/23/20030.3195767660.365696105
10/24/20030.3151516550.36529699
10/27/20030.3151354410.365901666
10/28/20030.3142884050.365839302
10/29/20030.3142822270.3640955
10/30/20030.3142807450.363681904
10/31/20030.315021930.363453814
11/3/20030.3149167290.362960075
11/4/20030.3147831270.362684286
11/5/20030.3146563180.362129068
11/6/20030.3143280550.359455301
11/7/20030.3138141510.35967231
11/10/20030.3149212730.358688956
11/11/20030.3157029970.358445754
11/12/20030.3152422660.358239571
11/13/20030.3154568530.357913949
11/14/20030.3166387670.358119589
11/17/20030.3174647880.356989507
11/18/20030.3173742690.354504357

 

The want table is:

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

Re: Merging tables based on dates and names

Posted in reply to Agent1592

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

Respected Advisor
Posts: 4,173

Re: Merging tables based on dates and names

Posted in reply to Agent1592

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

 

Contributor
Posts: 36

Re: Merging tables based on dates and names

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.

Super User
Posts: 7,854

Re: Merging tables based on dates and names

Posted in reply to Agent1592

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-dat... to convert your datasets to datastep code, and post that according to https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 36

Re: Merging tables based on dates and names

Posted in reply to KurtBremser

I tried to attach SAS files earlier.

Attachment
Solution
2 weeks ago
Super User
Posts: 7,854

Re: Merging tables based on dates and names

Posted in reply to Agent1592

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 36

Re: Merging tables based on dates and names

Posted in reply to KurtBremser

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 137 views
  • 1 like
  • 3 in conversation