BookmarkSubscribeRSS Feed
mhamlett
Quartz | Level 8

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;

1 REPLY 1
mhamlett
Quartz | Level 8

this is occuring in the data prep tool

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 1 reply
  • 625 views
  • 0 likes
  • 1 in conversation