Combine two table in one excel report

[Ken]

To All SAS Experts,

I am working on below excercise and do not know what is missing.

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

Question is to get:
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

Problem:
Below are the two codes for Merging Two Tables and still not getting desired output.

To generate output as below:
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

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 - optional
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;

Thnks
Tech

 

[Ken]

Hi All,

I have solved my problem. How to delete this thread.

Tech