BSL Tracker

Re: Decision Tree and Forecasting in VA

BSL — Tue, 27 Jan 2015 05:53:26 GMT

thanks anna 🙂

Re: Decision Tree and Forecasting in VA

BSL — Thu, 22 Jan 2015 09:30:29 GMT

Does anyone has the standard sample data for decision tree chart?

thnx

grouping two tables

BSL — Wed, 17 Dec 2014 14:15:11 GMT

Hi All,

How can I group two tables and make a single table in SAS however both tables do not have the same number of records. First table is for Sale and another one is for Target so I have .

Regards

Rahul

problem in grouping the data

BSL — Mon, 15 Dec 2014 13:43:03 GMT

Dear Experts,

I am trying to group two table TGT and SALE. TGT has grouped data and SALE has detailed level data so could anybody please help me to group these tables correctly. I am using below mentioned code.

Sample data is attached in book.xls

Regards

Rahul

proc sql;

SELECT TgtYearMon, TgtYearMon_m, TgtYearMon_Y, TgHQCode, GrpCode, GrpSubCode,TgtQty, TgtQty_Avg, TgtRate, TgTSaSMGrp, TgTSaSSGrp,

UnitCode, Iddepotcode, DepotName, IdPrdCode, IdPrdQty, SaleKg, IdPrdAmt, AccName, Iddate, ImDate, CityDesc, StateName, UomDesc, PrdAsgnCode,

PrdName, PrdUOM, EmpName,ImNetAmtRO, PrdGrpCode, PrdWt, AccCity, IdPrdRate, Prd1KgGEq, IdPrdQtyF, ImType, IdType, UMeasure, NQty, value,

AccSLgrpCatg, AccSlGrpZone, PrdSaSMGrp, PrdSaSSGrp

FROM ( SELECT *

FROM sw_sale_ssf

WHERE (vwInvoiceData_SSF.IdType = 'S') AND (vwAccountMaster_SSF.AccSaleGrp <> 'XX')

AND (vwAccountMaster_SSF.AccSaleGrp <> 'GOC')

isnull(vwInvoiceData_SSF.Iddepotcode = 'ERP' OR

vwInvoiceData_SSF.Iddepotcode IS NULL) and

and (vwInvoiceData_SSF.Iddate between '2014-04-01' and '2014-11-30')

GROUP BY Iddepotcode, IdPrdCode, AccName, Iddate, ImDate, CityDesc, StateName, UomDesc, PrdAsgnCode, PrdName, PrdUOM, EmpName, ImNetAmtRO,

PrdGrpCode, PrdWt, AccCity, Prd1KgGEq, IdPrdQtyF, ImType, IdType, AccSLgrpCatg, AccSlGrpZone, PrdSaSMGrp, PrdSaSSGrp

UNION

SELECT TgtYearMon, TgtYearMon_m, TgtYearMon_Y, TgHQCode, GrpCode, '0' AS TgtQty, TgtQty_Avg, TgtRate, TgTSaSMGrp, TgTSaSSGrp,

'SSF' AS UnitCode, ' ' AS Iddepotcode, ' ' as DepotName, cast(0 AS decimal(18, 2)) AS IdPrdCode,

cast(0 AS decimal(18, 2)) AS IdPrdQty, cast(0 AS decimal(18, 2)) AS SaleKg, cast(0 AS decimal(18, 2)) AS IdPrdAmt,

' ' AS AccName, cast(0 AS decimal(18, 2)) AS Iddate, cast(0 AS decimal(18, 2)) AS ImDate, ' ' AS CityDesc, ' ' AS StateName,

' ' AS UomDesc, ' ' AS PrdAsgnCode, ' ' AS PrdName, ' ' AS PrdUOM, ' ' AS EmpName, cast(0 AS decimal(18, 2)) AS ImNetAmtRO,

cast(0 AS decimal(18, 2)) AS PrdGrpCode, cast(0 AS decimal(18, 2)) AS PrdWt, ' ' AS AccCity, cast(0 AS decimal(18, 2)) as IdPrdRate,

cast(0 AS decimal(18, 2)) as Prd1KgGEq, cast(0 AS decimal(18, 2)) as IdPrdQtyF, ' ' AS ImType, ' ' AS IdType, 'Q' AS UMeasure,

cast(0 AS decimal(18, 2)) AS NQty, cast(0 AS decimal(18, 2)) AS value, ' ' AS AccSLgrpCatg, ' ' AS AccSlGrpZone,

' ' AS PrdSaSMGrp, ' ' AS PrdSaSSGrp

FROM V_Sas_SaleTgtMst

where V_Sas_SaleTgtMst.TgtYearMon between '01Apr2014' and '30Nov2014') AS tgtsale;

quit;

Re: use of ( ) in the following code

BSL — Fri, 05 Dec 2014 07:24:40 GMT

please see the attached original code where I found these parenthesis used at the bottom.

use of ( ) in the following code

BSL — Fri, 05 Dec 2014 06:26:17 GMT

hi,

I am not able to understand the use of parenthesis ( ) in the following code can any explain it to me.

data temp ( );

set table1 ( ) table2 ( ) table3 ( );

run;

-rahul

Puzzled in query

BSL — Sat, 15 Nov 2014 14:00:14 GMT

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

Re: Nested query of sum for grouped average

BSL — Thu, 13 Nov 2014 05:17:26 GMT

There is slightly changes in the data...so can it be achieved in SAS.

I am posting a new Question now.

Nested query of sum for grouped average

BSL — Wed, 12 Nov 2014 13:03:26 GMT

Hi All,

I am trying to get the sum of averaged and grouped data for below mention sample data co could anybody please help to to correct the code. I am trying

to achieve the same result with below mentioned code:

proc sql;

select date, shift, sum(aiprn) as SIPRN, qty, sales

from (select date, shift, avg(iprn) as AIPRN, qty, sales from test group by date, shift)

group by date, shift;

quit;

HAVE**********************************

DATE SHIFT IPRN QTY SALE

05Jan2014 Day 12 1 55

05Jan2014 Day 11 3 44

05Jan2014 Day 10 4 33

05Jan2014 Night 07 1 54

05Jan2014 Night 07 6 43

06Jan2014 Day 08 6 23

06Jan2014 Day 12 5 12

06Jan2014 Night 07 8 41

06Jan2014 Night 09 4 15

06Jan2014 Night 11 1 38

WANT*********************************

DATE SHIFT IPRN QTY SALE

05Jan2014 Day 18 1 55

05Jan2014 Day 18 3 44

05Jan2014 Day 18 4 33

05Jan2014 Night 18 1 54

05Jan2014 Night 18 6 43

06Jan2014 Day 19 6 23

06Jan2014 Day 19 5 12

06Jan2014 Night 19 8 41

06Jan2014 Night 19 4 15

06Jan2014 Night 19 1 38

Regards

Rahul