Date:         Sun, 10 Aug 2003 22:40:13 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Merging Files with different Date format
Comments: To: "Philip_Crane@WORKCOVER.VIC.GOV.AU"
          <Philip_Crane@WORKCOVER.VIC.GOV.AU>
Content-Type: text/plain

Wouldn't you agree that a bit less academic solution to an academic problem
would use the informats to convert csv date values to SAS (numeric) date
values, and would use a full join to capture dates and values that do not
match? A query along these lines .....

    create table final as
        select coalesce(date1,date2) as date,
                value1,
                value2
           from file1 as  o full join file2 as  t
             on o.date1 = t.date2
        ;
    quit;

Database programming has so many different levels! Good practice, I believe,
forces us to go beyond stated requirements.

Sig

-----Original Message-----
From: Philip_Crane@WORKCOVER.VIC.GOV.AU
To: SAS-L@LISTSERV.UGA.EDU
Sent: 8/10/2003 10:14 PM
Subject: Re: Merging Files with different Date format

Santosh

The following will achieve what you want.

Philip

     1  options nocenter;
     2
     3  /* Read in the data, use the input function and an informat to
*/
     4  /* convert to a SAS date then the put function with a format to
*/
     5  /* convert back to text. Use text values to merge on because I
*/
     6  /* am uncertain that the two informats will return exactly the
*/
     7  /* same number for the same date.
*/
     8
     9  data file1;
    10     length date1  $ 10;
    11
    12     infile 'file1.csv' dsd
    13            truncover;
    14     input date1      $
    15           value1
    16           ;
    17    dt_1 = put(input(date1,mmddyy10.),ddmmyys10.);
    18  run;
    19
    20  data file2;
    21     length date2  $ 11;
    22
    23     infile 'file2.csv' dsd    24            truncover;
    25     input date2      $
    26           value2
    27           ;
    28   dt_2 = put(input(date2,date11.),ddmmyys10.);
    29  run;
    30
    31  /* Use sql to merge the two files.                           */
    32
    33  proc sql;
    34     create table final as
    35     select dt_1,
    36            date1,
    37            date2,
    38            value1,
    39            value2
    40       from file1   o,
    41            file2   t
    42      where o.dt_1 = t.dt_2
    43            ;
    44  quit;
    45
    46  proc print data=final;



                      Santosh Mishra
                      <ucrecon111@YAHOO        To:
SAS-L@LISTSERV.UGA.EDU
                      .COM>                    cc:
                      Sent by: "SAS(r)         Subject:  Merging Files
with different Date format
                      Discussion"
                      <SAS-L@LISTSERV.U
                      GA.EDU>


                      11/08/2003 11:07
                      AM
                      Please respond to
                      Santosh Mishra






Dear Friends,
 I have two files with data of the following nature.

file1.csv is a comma delimited file of the following
nature

Date            Value
1/4/1990,       67.5
1/5/1990,        67.9
1/10/1990,      62.5
1/15/1990,     123.5

file2.csv is of the follwong format

Date            Value
4-Jan-1990,        67.5
5-Jan-1990,       67.9
15-Jan-1990,        62.5
21-Jan-1990,        123.5

I want to merge the above two files  and create a
dataset such that there are two column of the data for
the dates (to be outputted too) that appears in both
the files.

Thanks in Advance.
Ciao,
Santosh Mishra
Department of Economics
UC Riverside





=====


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com