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
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
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;
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
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;
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
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
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
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
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
Simply replace
merge table_A(in=A) table_B(in=B);
with
merge A(in=A) B(in=B);
it should work.
PG
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....
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
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
SOrry PGStats,
i was referring to month of jan not April ...
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
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.