Hi Experts,
I am stuck in the following query as it has so many inner joins in from clause could anybady explain it to me so that I would be able to move further....
SELECT SAS_V_PrdMst.PrdGrpCode, SAS_V_PrdMst.PrdSubGrpCode, MONTH(SAS_V_FgTData.Iddate) AS M, YEAR(SAS_V_FgTData.Iddate) AS Y,
SUM(SAS_V_FgTData.IdPrdQty) AS SaleQ, SUM(SAS_V_FgTData.IdPrdQty * 0) AS TargetQ, SAS_V_SeMst.SEName
FROM SAS_V_GrpMst INNER JOIN
SAS_V_FgTData INNER JOIN
SAS_V_DealerMst ON SAS_V_FgTData.IdDealerCode = SAS_V_DealerMst.dealerCode INNER JOIN
SAS_V_PrdMst ON SAS_V_FgTData.IdPrdCode = SAS_V_PrdMst.PrdCode ON SAS_V_GrpMst.GrpCode = SAS_V_PrdMst.PrdGrpCode AND
SAS_V_GrpMst.GrpSubCode = SAS_V_PrdMst.PrdSubGrpCode INNER JOIN
SAS_V_SeMst ON SAS_V_DealerMst.DealerSECode = SAS_V_SeMst.SECode
WHERE (MONTH(SAS_V_FgTData.Iddate) = 4) AND (YEAR(SAS_V_FgTData.Iddate) = 2014) AND (SAS_V_FgTData.IdType IN ('S', 'US'))
GROUP BY SAS_V_PrdMst.PrdGrpCode, SAS_V_PrdMst.PrdSubGrpCode, MONTH(SAS_V_FgTData.Iddate), YEAR(SAS_V_FgTData.Iddate), SAS_V_SeMst.SEName
Regards
Rahul
I don't know what it does (I would have to guess what the fields actually represent) but reordering, using aliasses, and indenting properly might help:
SELECT
Prd.PrdGrpCode,
Prd.PrdSubGrpCode,
MONTH(FgT.Iddate) AS M,
YEAR(FgT.Iddate) AS Y,
Se.SEName,
SUM(FgT.IdPrdQty) AS SaleQ,
SUM(FgT.IdPrdQty * 0) AS TargetQ
FROM
SAS_V_GrpMst as Grp INNER JOIN
SAS_V_PrdMst as Prd
ON Grp.GrpCode = Prd.PrdGrpCode AND
Grp.GrpSubCode = Prd.PrdSubGrpCode INNER JOIN
SAS_V_FgTData as FgT
ON FgT.IdPrdCode = Prd.PrdCode INNER JOIN
SAS_V_DealerMst as Dealer
ON FgT.IdDealerCode = Dealer.dealerCode INNER JOIN
SAS_V_SeMst as Se
ON Dealer.DealerSECode = Se.SECode
WHERE
MONTH(FgT.Iddate) = 4 AND
YEAR(FgT.Iddate) = 2014 AND
FgT.IdType IN ('S', 'US')
GROUP BY
Prd.PrdGrpCode,
Prd.PrdSubGrpCode,
MONTH(FgT.Iddate),
YEAR(FgT.Iddate),
Se.SEName;
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.