Merging 2 tables

[BETO]

Hi,

i have  2 tables that I would like to merge Into a new 3rd table. table1 has "A" service only and table 2 has " B"service only. What I'm looking to do is count how many A-service were done and how many B-service were done in addition how many times service A-B were done on the same date.

table-A

Date.           ID.     Service

01/01/13     5A.      Service A

01/02/13.     6A.      Service A

table-B

Date.           ID.     Service

01/01/13     5A.      Service B

01/03/13.     6A.      Service B

final table

ID.      Service.    Date

5A.        2 A/B.      01/01/13

6A.        1 A.          01/02/13

6A.        1B.             01/03/13

Thank you in advance

[Patrick]

Something like below might do:

data table_A;
  infile datalines truncover;
  input date anydtdte8. id $ service $9.;
  format date date9.;
  count=1;
  datalines;
01/01/13 5A Service A
01/02/13 6A Service A
01/02/13 6A Service A
;
run;

data table_B;
  infile datalines truncover;
  input date anydtdte8. id $ service $9.;
  format date date9.;
  count=1;
  datalines;
01/01/13 5A Service B
01/03/13 6A Service B
;
run;

proc sql;
  create table want as 
  select distinct id,date,catx(' ',sum(count),service) as service
  from
    (
      select
        coalesce(a.id,b.id) as id,
        coalesce(a.date,b.date) as date format=date9.,
        catx('/',scan(a.service,-1),scan(b.service,-1)) as service length=6,
        sum(a.count,b.count) as count
      from table_A a full outer join table_B b
        on a.id=b.id and a.date=b.date
    )
  group by id,date
  ;
quit;

[BETO]

THanks patrick for responding  I tested the code you provided an its exactly what I need. the issue  is how can i make refer to the  2 tables (cols). Each table has over  50k records how can i make refer to table?the in files and datelines make refer to the examples ... Thanks again

[Patrick]

If this is a one off job and you're using SAS EG then you could simply use the EG import wizard. Else you would need some code similar to below.

filename myfileA '<full path to your file, eg: c:\temp\thisfile.txt';

data table_A;

  infile myfileA truncover dlm='<delimiter, eg. a comma for csv or a blank as in your example data>';

  input date anydtdte8. id $ service $9.;

  format date date9.;

  count=1;

run;

[UrvishShah]

Hi,

I think it is eassy and faster by using data step...

proc sort data = table_A;

     by date;

run;

proc sort data = table_B;

     by date;

run;

data final(drop = n);

    retain id service date;

    merge table_A(in = a)

       table_B(in = b);

n = 1;

by date;

if a and b then do;

    n + 1;

    if n = 2 then service = put(n,1.)||" A/B";   

end;

if n = 1 then service = put(n,1.)||" "||strip(substr(service,8,1));

run;

-Urvish

[BETO]

HI UrvishShah,

thank you for responding. I used your snippet of code and it seem to only show me 2a/b as the output.

in some cases I will only have 1 A or 1 B  done on that day . At first run the output indicated I had A/B done on same day when the Id is set up to only do A.... The type of service are Dep and Rep. I made change to code to look for D or R same results.  Thanks again

p

proc sort data = table_A;

     by date;

run;

proc sort data = table_B;

     by date;

run;

data final(drop = n);

    retain id service date;

    merge table_A(in = A)

       table_B(in = B);

n = 1;

by date;

if a and b then do;

    n + 1;

    if n = 2 then service = put(n,1.)||" A/B"; 

end;

if n = 1 then service = put(n,1.)||" "||strip(substr(service,8,1));

run;

MERGE statement has more than one data set with repeats of BY values.

NOTE: There were 14580 observations read from the data set WORK.TABLE_A.

NOTE: There were 21789 observations read from the data set WORK.TABLE_B.

NOTE: The data set WORK.FINAL has 22448 observations and 4 variables.

NOTE: DATA statement used (Total process time):

      real time           1.57 seconds

      cpu time            0.01 seconds

[UrvishShah]

Hello,

As per your sample data, code which i have posted will work but if there is many to many relationship b/w your type of services and date then try the following SAS code...i have corrected it as per your requirement...

proc sort data = table_A;

    by date id;

run;

proc sort data = table_B;

     by date id;

run;

data final;

   retain id service date;

   merge table_A(in = a)

             table_B(in = b);

   n = 1;

   by date id;

   if a and b then do;

      n + 1;

      n + n;

   end;

   if n > 1 then service = put(n,1.)||" A/B";

   if a not eq b then do;

      n + 1;

      service = put(n,1.)||" "||strip(substr(service,8,1));

   end;

   n = 1;

   if first.date EQ last.date then service = put(n,1.)||" "||strip(substr(service,2));

run;

proc sort data = final(drop = n) nodupkey;

    by id service date;

run;

I hope this time this will produce the output as per your requirement...And note regarding MERGE statement is obivious as you are having many to many relations in your data...

Thanks,

Urvish

[PGStats]

I assumed that the number in output field Service is the total number of cases from either table that are present for a given ID and date :

data table_A;
infile datalines truncover;
input date anydtdte8. id $ service $9.;
format date date9.;

datalines;
01/01/13 5A Service A
01/02/13 6A Service A
01/02/13 6A Service A
;

data table_B;
infile datalines truncover;
input date anydtdte8. id $ service $9.;
format date date9.;

datalines;
01/01/13 5A Service B
01/03/13 6A Service B
;

data both(keep=id date inA inB);
merge table_A(in=A) table_B(in=B); by id date;
inA = a; inB = b;
run;

proc sql;
create table final as
select id, date,
     catx(" ", (inA+inB)*n, case when(inA and inB) then "A/B" when inA then "A" else "B" end) as service
from
(    select id, date, inA, inB, count(*) as n
     from both
     group by id, date, inA, inB
);
select * from final;
quit;

PG

[BETO]

HI PGStats,

TO make your script work for me how do I make refer to table a or table b ? Both tables are imported to lib. Name work  At the beginning of the script.

FYI-i have a 2 static tables that keeps historical data which I import  into sas by selecting last 30 days  which intern makes tableA table B . If can make refer to those tables with you're snippet of code I will be golden thanks you again

[PGStats]

Simply replace

merge table_A(in=A) table_B(in=B);

with

merge A(in=A) B(in=B);

it should work.

PG

[BETO]

HI PGStats,

you were correct it was a simple to fix thanks ... What I noticed happen is that it only match 1 day  worth of data  the 04/01/13   I have 8 days worth of  data  in both tables i least expected a 1 to appear  .how can I bring the rest of the days ? I thought since it was a merge data  step everything would be brought over to table  name both. I think we are really close  thanks again for your help....

[PGStats]

I modified the test data like this :

data A;
  infile datalines truncover;
  input date anydtdte8. id $ service $9.;
  format date date9.;

  datalines;
01/01/13 5A Service A
01/01/13 5A Service A
01/02/13 6A Service A
01/02/13 6A Service A
;

data B;
  infile datalines truncover;
  input date anydtdte8. id $ service $9.;
  format date date9.;

  datalines;
01/01/13 5A Service B
01/01/13 5A Service B
01/03/13 6A Service B
;

and got the following result:

id             date  service
----------------------------
5A        01JAN2013  4 A/B
6A        01FEB2013  2 A
6A        01MAR2013  1 B

and the note:

NOTE: MERGE statement has more than one data set with repeats of BY values.

which seems to be the behaviour that you expect. Check that your tables A and B are both sorted BY ID DATE.

PG

[BETO]

THanks for responding PGStats. In the output that you provided it only brought over 04/01/13 data A and Data b has other  dates  04/02/13 and 04/03/13 which would be nice if those came over as we'll  ... Thanks for the assistance

[BETO]

SOrry PGStats,

i was referring  to month of jan not April ...

[PGStats · Accepted solution]

On my system, the dates from the above example read as DD/MM/YY. It is possible that on your system they read as MM/DD/YY. Change the data accordingly or set system option

OPTION DATESTYLE=DMY;

to see the same results I did.

If I set OPTION DATESTYLE=MDY; on my system, I get result

id             date  service

----------------------------

5A        01JAN2013  4 A/B

6A        02JAN2013  2 A

6A        03JAN2013  1 B

PG