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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.