Merging two table in single report

deleted_user — Sun, 25 Jul 2010 06:43:03 GMT

Request To All SAS Experts,
I am currently doing the below excercise and as you can see not that expert.

Purpose:
To get an monthly output and compare current with previous month plus the
output to be in Excel format.

Question is HowTo:
1) Display "Dispatch" value for "Sent-N" and "Sent-Y" alongwith "Total" on
same line with "Received"
2) Total to be displayed on Left.
3) Compare Current with previous month

Output is:
current month, prev. month and difference between current to prev. month
(Difficult to show in this thread output format- can email excel file on request)
Column
1) Total of Modes-Post, Modes-Courier, Total of Dispatched
2) Received value will be Modes-Post and Modes Courier
2a) Modes-Post value will be RcptDrtn (5d),RcptDrtn (15d)+(30d) and
Total of All rcptDrtn
2b) Modes Courier (Only Display Total of (5d))
3) Dispatch value will be Sent-N, Sent-Y and Total of Sent-N and Y

Month-Jan 2010
Total / Received /Dispatched
Modes-Post Modes-Courier
MaterialName"RcptDrtn(5d)" / "RcptDrtn(15d,30d)"/ Total Sent-N Sent-Y Total

Mtrl_A 4 1 1 2
1 1
Mtrl_B 5 2 2 1 1
1 2
......
Total
Problem:
Below are the two codes for Merging Two Tables and still not getting desired output.

Wanted to get values of RcptDrtn and SentYN value in one column if required R_Value and D_Value also in column to get expected output

Code 1
PROC SQL;
CREATE TABLE Work.MergeRecord AS
SELECT DISTINCT
MTRLRCVD.Category,
MTRLRCVD.Mtrl_Name,
MTRLRCVD.Modes,
MTRLRCVD.RcptDrtn,
MTRLDSPTCH.SentYN,
MTRLRCVD.R_Value as Received,
(CASE WHEN MTRLDSPTCH.Modes ="Post" THEN sum(MTRLDSPTCH.D_Value)
WHEN MTRLDSPTCH.Modes ="Courier" THEN sum(MTRLDSPTCH.D_Value)
ELSE 0
END
) AS Dispatch
FROM WORK.MTRLRCVD AS MTRLRCVD
FULL JOIN
WORK.MTRLDSPTCH AS MTRLDSPTCH
ON (MTRLRCVD.Modes = MTRLDSPTCH.Modes)
AND (MTRLRCVD.Category = MTRLDSPTCH.Category)
AND (MTRLRCVD.Mtrl_Name = MTRLDSPTCH.Mtrl_Name)
GROUP BY
MTRLRCVD.Category,
MTRLRCVD.Mtrl_Name,
MTRLRCVD.Modes
ORDER BY
MTRLRCVD.Category,
MTRLRCVD.Mtrl_Name,
MTRLRCVD.Modes;
QUIT;

Code 2
PROC SQL;
CREATE TABLE WORK.MergedTbl AS
SELECT DISTINCT t1.Mth,
t1.Category,
t1.Mtrl_Name,
t1.Modes,
t1.RcptDrtn as MergeFld,
t1.R_Value as OneValue
FROM WORK.MTRLRCVD AS t1
union
SELECT DISTINCT t2.Mth,
t2.Category,
t2.Mtrl_Name,
t2.Modes,
t2.SentYN as MergeFld,
t2.D_Value as OneValue
FROM WORK.MTRLDSPTCH AS t2
Order by
t1.Category,
t1.Mtrl_Name,
t1.Modes
Where t1.mth EQ t2.mth
QUIT;

///////////////////////////////////////////////////////

Below code is to create table and inserting data and is working
proc sql;
create table MTRLRCVD
(Category char(6) LABEL='Category',
Mth char(6) LABEL='Month',
Mtrl_Name Char(10) LABEL='Material',
Modes Char(8) LABEL='Modes',
RcptDrtn Char(3) LABEL='RcptDrtn',
R_Value Decimal(4));
quit;

proc sql;
create table MTRLDSPTCH
(Category char(6) LABEL='Category',
Mth char(6) LABEL='Month',
Mtrl_Name Char(10) LABEL='Material',
Modes Char(8) LABEL='Modes',
SentYN Char(3) LABEL='SentYN',
D_Value Decimal(2));
quit;

proc sql;
insert into MTRLRCVD values('Ctgy_1','Jan-10','Mtrl_A','Post','15d',1);
insert into MTRLRCVD values('Ctgy_1','Jan-10','Mtrl_A','Courier','5d',2);
insert into MTRLRCVD values('Ctgy_2','Jan-10','Mtrl_B','Post','30d',2);
insert into MTRLRCVD values('Ctgy_2','Jan-10','Mtrl_B','Courier','5d',1);
insert into MTRLRCVD values('Ctgy_2','Jan-10','Mtrl_C','Post','5d',5);
insert into MTRLRCVD values('Ctgy_7','Jan-10','Mtrl_D','Post','30d',4);
insert into MTRLRCVD values('Ctgy_1','Feb-10','Mtrl_A','Post','15d',3);
insert into MTRLRCVD values('Ctgy_1','Feb-10','Mtrl_A','Courier','5d',1);
insert into MTRLRCVD values('Ctgy_2','Feb-10','Mtrl_B','Post','30d',2);
insert into MTRLRCVD values('Ctgy_2','Feb-10','Mtrl_B','Courier','5d',3);
insert into MTRLRCVD values('Ctgy_2','Feb-10','Mtrl_C','Post','5d',7);
insert into MTRLRCVD values('Ctgy_7','Feb-10','Mtrl_D','Post','30d',3);
quit;

proc sql;
insert into MTRLDSPTCH values('Ctgy_1','Jan-10','Mtrl_A','Post','Y',1);
insert into MTRLDSPTCH values('Ctgy_2','Jan-10','Mtrl_B','Post','N',1);
insert into MTRLDSPTCH values('Ctgy_2','Jan-10','Mtrl_B','Courier','Y',1);
insert into MTRLDSPTCH values('Ctgy_2','Jan-10','Mtrl_B','Courier','Y',1);
insert into MTRLDSPTCH values('Ctgy_4','Jan-10','Mtrl_C','Courier','N',1);
insert into MTRLDSPTCH values('Ctgy_7','Jan-10','Mtrl_D','Post','N',1);
insert into MTRLDSPTCH values('Ctgy_1','Feb-10','Mtrl_A','Post','Y',1);
insert into MTRLDSPTCH values('Ctgy_2','Feb-10','Mtrl_B','Post','N',0);
insert into MTRLDSPTCH values('Ctgy_2','Feb-10','Mtrl_B','Courier','Y',1);
insert into MTRLDSPTCH values('Ctgy_2','Feb-10','Mtrl_B','Courier','Y',0);
insert into MTRLDSPTCH values('Ctgy_4','Feb-10','Mtrl_C','Courier','N',1);
insert into MTRLDSPTCH values('Ctgy_7','Feb-10','Mtrl_D','Post','N',1);
quit;

Tain

Re: Merging two table in single report

Patrick — Tue, 27 Jul 2010 09:01:05 GMT

Hi Tain

The following SQL creates the Work.MergeRecord in the form you might want it. Hope this brings you a step further in solving your exercise.

proc sql;
CREATE TABLE Work.MergeRecord AS
select
coalesce(l.Category,r.Category) as Category LABEL='Category' length=6 format=$6.,
coalesce(l.Mth,r.Mth) as Mth LABEL='Month' length=6 format=$6.,
coalesce(l.Mtrl_Name,r.Mtrl_Name) as Mtrl_Name LABEL='Material' length=10 format=$10.,
coalesce(l.Modes,r.Modes) as Modes LABEL='Modes' length=8 format=$8.,
l.RcptDrtn,
l.R_Value,
r.SentYN,
r.D_Value
from MTRLRCVD as l,MTRLDSPTCH as r
where l.Category=r.Category and l.Mth=r.Mth and l.Mtrl_Name=r.Mtrl_Name and l.Modes=r.Modes
;
quit;

HTH
Patrick P.S: Doing this in a data step merge would have been less coding but I assumed you need it solved in SQL.

Message was edited by: Patrick

topic Merging two table in single report in SAS Enterprise Guide

Merging two table in single report

Re: Merging two table in single report