topic Re: Merging 2 tables in SAS Procedures

Merging 2 tables

BETO — Fri, 19 Apr 2013 06:32:45 GMT

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

Re: Merging 2 tables

Patrick — Fri, 19 Apr 2013 08:29:09 GMT

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;

Re: Merging 2 tables

UrvishShah — Fri, 19 Apr 2013 11:15:21 GMT

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

Re: Merging 2 tables

BETO — Fri, 19 Apr 2013 19:15:42 GMT

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

Re: Merging 2 tables

Patrick — Fri, 19 Apr 2013 23:19:10 GMT

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;

Re: Merging 2 tables

BETO — Sat, 20 Apr 2013 18:28:29 GMT

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

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

Re: Merging 2 tables

PGStats — Sat, 20 Apr 2013 20:23:54 GMT

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;

Re: Merging 2 tables

BETO — Sun, 21 Apr 2013 02:15:01 GMT

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

Re: Merging 2 tables

PGStats — Sun, 21 Apr 2013 03:43:45 GMT

Simply replace

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

with

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

it should work.

Re: Merging 2 tables

BETO — Mon, 22 Apr 2013 00:16:53 GMT

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

Re: Merging 2 tables

PGStats — Mon, 22 Apr 2013 01:49:38 GMT

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.

Re: Merging 2 tables

BETO — Mon, 22 Apr 2013 02:07:44 GMT

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

Re: Merging 2 tables

BETO — Mon, 22 Apr 2013 02:14:13 GMT

SOrry PGStats,

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

Re: Merging 2 tables

PGStats — Mon, 22 Apr 2013 02:41:33 GMT

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

Re: Merging 2 tables

BETO — Mon, 22 Apr 2013 04:59:01 GMT

THank you so much PGStats. I added the option and it worked...

Re: Merging 2 tables

UrvishShah — Mon, 22 Apr 2013 06:10:54 GMT

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