Help using Base SAS procedures

proc transpose incorrect results

Reply
Super Contributor
Posts: 275

proc transpose incorrect results

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

 

 

 

Super User
Super User
Posts: 7,955

Re: proc transpose incorrect results

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?

Super Contributor
Posts: 275

Re: proc transpose incorrect results

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

SAS Super FREQ
Posts: 296

Re: proc transpose incorrect results

[ Edited ]

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

Super Contributor
Posts: 275

Re: proc transpose incorrect results

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

Ask a Question
Discussion stats
  • 4 replies
  • 559 views
  • 0 likes
  • 3 in conversation