BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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.


 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Agent1592
Pyrite | Level 9

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 
Agent1592
Pyrite | Level 9

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

Patrick
Opal | Level 21

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

 

Agent1592
Pyrite | Level 9

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.

Agent1592
Pyrite | Level 9

I tried to attach SAS files earlier.

Kurt_Bremser
Super User

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.

Agent1592
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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