va7.3 sub select in a select statement not working

mhamlett — Mon, 13 Jun 2016 20:48:01 GMT

the below code on returns in the ENTITYNAME only returns one value for every record in the primary file.

any ideas why?

proc sql noprint;
create table TEMP_LASR_VIEW_1359 as
SELECT
DQ_CSQbySalesMeals.UnitName length=75 format=$75. AS UnitName,
DQ_CSQbySalesMeals.UnitNo length=4 format=$4. AS UnitNo,
DQ_CSQbySalesMeals.UnitStateCode length=2 format=$2. AS UnitStateCode,
DQ_CSQbySalesMeals.UnitZipCode length=5 format=$5. AS UnitZipCode,
DQ_CSQbySalesMeals.UnitCompFranCode length=4 format=$4. AS UnitCompFranCode,
DQ_CSQbySalesMeals.AccountingYear length=4 format=$4. AS AccountingYear,
DQ_CSQbySalesMeals.AccountingPeriod length=2 format=$2. AS AccountingPeriod,
SUM(DQ_CSQbySalesMeals.WeeklySalesTotal) length=8 format=F13.2 AS WeeklySalesTotal_SUM,
SUM(DQ_CSQbySalesMeals.WeeklyMealsTotal) length=8 format=F11. AS WeeklyMealsTotal_SUM,
AVG(DQ_CSQbySalesMeals.CSQScore_AVG) length=8 format=F14.2 AS CSQScore_AVG_AVG,
DQ_CSQbySalesMeals.UnitDivision length=1 format=$1. AS UnitDivision,
DQ_CSQbySalesMeals.UnitDistrict length=2 format=$2. AS UnitDistrict,
DQ_CSQbySalesMeals.UnitEOP length=3 format=$3. AS UnitEOP,
DQ_CSQbySalesMeals.UnitOpenDate length=8 format=DATE9. AS UnitOpenDate,
DQ_CSQbySalesMeals.UnitStandardMetro length=1 format=$1. AS UnitStandardMetro,
DQ_CSQbySalesMeals.UnitBuildingType length=10 format=$10. AS UnitBuildingType,
DQ_CSQbySalesMeals.UnitManagerName length=50 format=$50. AS UnitManagerName,
SUM(DQ_CSQbySalesMeals.WeeklySalesLunch) length=8 format=F13.2 AS WeeklySalesLunch_SUM,
SUM(DQ_CSQbySalesMeals.WeeklySalesDinner) length=8 format=F13.2 AS WeeklySalesDinner_SUM,
SUM(DQ_CSQbySalesMeals.WeeklySalesOther) length=8 format=F13.2 AS WeeklySalesOther_SUM,
SUM(DQ_CSQbySalesMeals.WeeklyMealsBreakfast) length=8 format=F11. AS WeeklyMealsBreakfast_SUM,
SUM(DQ_CSQbySalesMeals.WeeklyMealsLunch) length=8 format=F11. AS WeeklyMealsLunch_SUM,
SUM(DQ_CSQbySalesMeals.WeeklyMealsDinner) length=8 format=F11. AS WeeklyMealsDinner_SUM,
SUM(DQ_CSQbySalesMeals.WeeklySalesBreakfast) length=8 format=F13.2 AS WeeklySalesBreakfast_SUM,
DQ_CSQbySalesMeals.AccountingPeriodFull length=9 format=$9. AS AccountingPeriodFull,
DQ_CSQbySalesMeals.YearNo length=8 format=F11. AS YearNo,
DQ_CSQbySalesMeals.PeriodNo length=8 format=F11. AS PeriodNo,
(select
FranchiseEntityName1
FROM
VALIBLA.BI_FRANCHISEDIRECTORY_STORES BI_FRANCHISEDIRECTORY_STORES
WHERE
BI_FRANCHISEDIRECTORY_STORES.UnitNo EQ DQ_CSQbySalesMeals.UnitNo ) length=50 format=$CHAR50. label='EntityName' AS EntityName
FROM
VALIBLA.DQ_CSQbySalesMeals DQ_CSQbySalesMeals
WHERE
DQ_CSQbySalesMeals.UnitNo IN (
'0678' ,'0675'
)
GROUP BY
DQ_CSQbySalesMeals.UnitName,
DQ_CSQbySalesMeals.UnitNo,
DQ_CSQbySalesMeals.UnitStateCode,
DQ_CSQbySalesMeals.UnitZipCode,
DQ_CSQbySalesMeals.UnitCompFranCode,
DQ_CSQbySalesMeals.AccountingYear,
DQ_CSQbySalesMeals.AccountingPeriod,
DQ_CSQbySalesMeals.UnitDivision,
DQ_CSQbySalesMeals.UnitDistrict,
DQ_CSQbySalesMeals.UnitEOP,
DQ_CSQbySalesMeals.UnitOpenDate,
DQ_CSQbySalesMeals.UnitStandardMetro,
DQ_CSQbySalesMeals.UnitBuildingType,
DQ_CSQbySalesMeals.UnitManagerName,
DQ_CSQbySalesMeals.AccountingPeriodFull,
DQ_CSQbySalesMeals.YearNo,
DQ_CSQbySalesMeals.PeriodNo,
(select
FranchiseEntityName1
FROM
VALIBLA.BI_FRANCHISEDIRECTORY_STORES BI_FRANCHISEDIRECTORY_STORES
WHERE
BI_FRANCHISEDIRECTORY_STORES.UnitNo EQ DQ_CSQbySalesMeals.UnitNo );
quit;

Re: va7.3 sub select in a select statement not working

mhamlett — Mon, 13 Jun 2016 20:51:45 GMT

this is occuring in the data prep tool

topic Re: va7.3 sub select in a select statement not working in SAS Visual Analytics

va7.3 sub select in a select statement not working

Re: va7.3 sub select in a select statement not working