Prev: Simple (clear) formatting in ExcelXP tagsets output
Next: SAS macros
From: Inat on 25 Jul 2010 02:57

Requesting for expert help
Currently doing below problem and as you can see not an expert.

Purpose:
To get monthly output, compare current with previous month and
output be in excel format.

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

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

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



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;


///////////////////////////////////////////////////////
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;
 | 
Pages: 1
Prev: Simple (clear) formatting in ExcelXP tagsets output
Next: SAS macros