topic proc transpose incorrect results in SAS Procedures

proc transpose incorrect results

saspert — Fri, 25 Sep 2015 14:53:25 GMT

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_ClSpots	SUM_of_GRPs	SUM_of_IMPs	SUM_of_Coupons	SUM_of_Visits	SUM_of_Tests	SUM_of_Store	SUM_of_BuyNow	Country
41454	15749.33	150875	165	6548	2395	286	87	Canada
86418	13212.221	4442280	3570.52	94548.62	42653.48	5575.02	1368	US

		value2	value3
Canada	BuyNow	0	0
ClSpots	0	0
Coupons	0	0
GRPs	89583.02	17723.35
IMPs	0	0
SPENT	43.72	.
Store	0	0
Tests	0	0
Visits		0
USA	BuyNow	.	.
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

Re: proc transpose incorrect results

RW9 — Fri, 25 Sep 2015 16:02:35 GMT

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?

Re: proc transpose incorrect results

saspert — Wed, 30 Sep 2015 19:02:34 GMT

@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.

Re: proc transpose incorrect results

Sam_SAS — Wed, 30 Sep 2015 20:30:29 GMT

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#n1lxnqfip132can1hdzuerib7i76.htm

Re: proc transpose incorrect results

saspert — Wed, 30 Sep 2015 21:57:52 GMT

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