BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

Hi,

My proc transpose code works fine but the results are not what I expected. To compare the dataset before and after transpose, I compared the summary being calculated and I get different results. 

 

simple counts of metrics before transpose

 

SUM_of_ClSpotsSUM_of_GRPsSUM_of_IMPsSUM_of_CouponsSUM_of_VisitsSUM_of_TestsSUM_of_StoreSUM_of_BuyNowCountry 
4145415749.331508751656548239528687Canada 
8641813212.22144422803570.5294548.6242653.485575.021368US

 

  value2 value3
CanadaBuyNow00
ClSpots00
Coupons00
GRPs89583.0217723.35
IMPs00
SPENT43.72.
Store00
Tests00
Visits 0
USABuyNow..
ClSpots..
Coupons..
GRPs..
IMPs..
SPENT..
Store..
Tests..
Visits..

 

the second table is summarization after transposing. My code is below - 

 


proc sql noprint;
create view TEMP_LASR_VIEW_1190 as
SELECT
VW_BB_TVDATA.Product length=255 format=$255. AS Product,
VW_BB_TVDATA.YearOf length=8 format=11. AS YearOf,
VW_BB_TVDATA.Month length=255 format=$255. AS Month,
VW_BB_TVDATA.WeekOf length=10 format=$10. AS WeekOf,
VW_BB_TVDATA.Market length=255 format=$255. AS Market,
VW_BB_TVDATA.Station length=255 format=$255. AS Station,
VW_BB_TVDATA.Length length=8 format=11. AS Length,
VW_BB_TVDATA.DPAired length=255 format=$255. AS DPAired,
VW_BB_TVDATA.DaypartAired length=255 format=$255. AS DaypartAired,
VW_BB_TVDATA.TapeAired length=255 format=$255. AS TapeAired,
VW_BB_TVDATA.TapeNameAired length=255 format=$255. AS TapeNameAired,
VW_BB_TVDATA.ProgramAired length=255 format=$255. AS ProgramAired,
VW_BB_TVDATA.DateAired length=10 format=$10. AS DateAired,
VW_BB_TVDATA.TimeAired length=255 format=$255. AS TimeAired,
VW_BB_TVDATA.URLAddress length=255 format=$255. AS URLAddress,
VW_BB_TVDATA.SPENT length=8 format=DOLLAR23.2 AS SPENT,
VW_BB_TVDATA.ClSpots length=8 format=11. AS ClSpots,
VW_BB_TVDATA.GRPs length=8 AS GRPs,
VW_BB_TVDATA.IMPs length=8 format=11. AS IMPs,
VW_BB_TVDATA.Coupons length=8 AS Coupons,
VW_BB_TVDATA.Visits length=8 AS Visits,
VW_BB_TVDATA.Tests length=8 AS Tests,
VW_BB_TVDATA.Store length=8 AS Store,
VW_BB_TVDATA.BuyNow length=8 AS BuyNow,
VW_BB_TVDATA.BuyType length=7 format=$7. AS BuyType,
VW_BB_TVDATA.country length=8 format=$255. AS country,
'crap' as crap
FROM
BLBFLDB.VW_BB_TVDATA VW_BB_TVDATA;
quit;


PROC SORT
DATA=WORK.TEMP_LASR_VIEW_1190(KEEP=SPENT ClSpots GRPs IMPs Coupons Visits Tests Store BuyNow Product YearOf Month WeekOf Market Station Length DPAired DaypartAired TapeAired TapeNameAired ProgramAired DateAired TimeAired
URLAddress buytype country
crap
)
OUT=WORK.SORTTempTableSorted
;
BY Product YearOf Month WeekOf Market Station Length DPAired DaypartAired TapeAired TapeNameAired ProgramAired DateAired TimeAired URLAddress buytype country
crap
;
RUN;


PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
OUT=TEMP_LASR_VIEW_1021(LABEL="Transposed BB TV table" drop=_label_)
PREFIX=value
NAME=metric
;
BY Product YearOf Month WeekOf Market Station Length DPAired DaypartAired TapeAired TapeNameAired ProgramAired DateAired TimeAired URLAddress buytype country
crap
;
VAR SPENT ClSpots GRPs IMPs Coupons Visits Tests Store BuyNow;

RUN; QUIT;

data TEMP_LASR_VIEW_1021 (drop=value1
crap
);
set TEMP_LASR_VIEW_1021;
if value1="crap" then delete;
/*else newmetric=input(value1,10.);*/
run;
TITLE; FOOTNOTE;

/* Drop existing table */
%vdb_dt(LASRLIB.LSR_BB_TV_TR);
data LASRLIB.LSR_BB_TV_TR ( );
set TEMP_LASR_VIEW_1021 ( );

 

I am running this in SAS VA 7.1

 

 

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, can't read that code, its all over the place - mix of casing returns in odd places etc.  Please format the code in a readable fashion, only provide the necessary code which is performing unexpectedly, provide test data in the form of a datastep, and what the expected output should look like.  I can't actually tell what the question is here?

saspert
Pyrite | Level 9

@RW9 I am hoping that this is the correct gist of the issue I am facing - 

I have non-additive measures like % (actual/planned) that need to be calculated in real-time without being able to pass any parameters back to the code. How do I do this in SAS VA 7.1? Example: I have a hierarchy of Country-state-county-zipcode. I need to be able to calculate a % figure at any level ( say country or county/zip or state/county/zipcode) and so on. There are many different possible ways to slice the data. I need the % to be recalculated at any possible combination.

Besides this measure, I also need a previous period's value (using a lag function) for all metrics at any level of grouping. Is this possible in SAS VA 7.1?

 

Thanks,
saspert. 

Sam_SAS
SAS Employee

Hi saspert,

You might want to create a new thread for the VA questions in the VA community.

My understanding is that non-additive measures are not really supported in the current releases of VA, so I don't think it can be done.

As for calculating the previous period's value, I believe that an aggregated measure using the RelativePeriod operator should work for what you want.

 

I think you would want an inferred interval, with a -1 offset.


The RelativePeriod operator is documented here:
https://support.sas.com/documentation/cdl/en/vaug/67500/HTML/default/viewer.htm#n1lxnqfip132can1hdzu...

saspert
Pyrite | Level 9

Thanks @Sam_SAS I need the comparison at a weekly level which is not supported by SAS VA 7.1. 😞

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1533 views
  • 0 likes
  • 3 in conversation