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;
... View more
