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.
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.
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:
| 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 |
The problem is not all the dates are matching so I was wondering if we can match the closest dates.
If you search the communities here with keywords like join by closest date you'll find tracks as below:
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.
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.
I tried to attach SAS files earlier.
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.
Yes I apologize, I included the SAS files. Had to ZIP the files first. The software would not let me upload SAS files directly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.