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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 640 views
  • 0 likes
  • 1 in conversation