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;
this is occuring in the data prep tool
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.