BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
1 REPLY 1
Patrick
Opal | Level 21
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 535 views
  • 0 likes
  • 2 in conversation