Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Creating transposed dataset in SAS VA 7.1

Reply
Super Contributor
Posts: 268

Creating transposed dataset in SAS VA 7.1

Hi,

 I have the following code in the data exploration custom code box -

 

/** QUERY **/

%LET VDB_GRIDHOST=server.client.com;
%LET VDB_GRIDINSTALLLOC=/opt/TKGrid;
options set=GRIDHOST="server.client.com";
options set=GRIDINSTALLLOC="/opt/TKGrid";
options validvarname=any validmemname=extend;

/* Register Table Macro */
%macro registertable( REPOSITORY=Foundation, REPOSID=, LIBRARY=, TABLE=, FOLDER=, TABLEID=, PREFIX= );

/* Mask special characters */

%let REPOSITORY=%superq(REPOSITORY);
%let LIBRARY =%superq(LIBRARY);
%let FOLDER =%superq(FOLDER);
%let TABLE =%superq(TABLE);

%let REPOSARG=%str(REPNAME="&REPOSITORY.");
%if ("&REPOSID." ne "") %THEN %LET REPOSARG=%str(REPID="&REPOSID.");

%if ("&TABLEID." ne "") %THEN %LET SELECTOBJ=%str(&TABLEID.);
%else %LET SELECTOBJ=&TABLE.;

%if ("&FOLDER." ne "") %THEN
%PUT INFO: Registering &FOLDER./&SELECTOBJ. to &LIBRARY. library.;
%else
%PUT INFO: Registering &SELECTOBJ. to &LIBRARY. library.;

proc metalib;
omr (
library="&LIBRARY."
%str(&REPOSARG.)
);
%if ("&TABLEID." eq "") %THEN %DO;
%if ("&FOLDER." ne "") %THEN %DO;
folder="&FOLDER.";
%end;
%end;
%if ("&PREFIX." ne "") %THEN %DO;
prefix="&PREFIX.";
%end;
select ("&SELECTOBJ.");
run;
quit;

%mend;

LIBNAME LASRLIB SASIOLA TAG=VAPUBLIC PORT=10031 HOST="server.client.com" SIGNER="https://server.client.com:443/SASLASRAuthorization" ;

option DBIDIRECTEXEC;

proc sql noprint;
create view TEMP_LASR_VIEW_0 as
SELECT
BB_MAY2015.'Week Of'n length=8 format=MMDDYY5. AS 'Week Of'n,
BB_MAY2015.Product length=4 format=$4. AS Product,
BB_MAY2015.'Year Of'n length=4 format=$4. AS 'Year Of'n,
BB_MAY2015.Month length=9 format=$9. AS Month,
BB_MAY2015.Market length=30 format=$30. AS Market,
BB_MAY2015.Station length=5 format=$5. AS Station,
BB_MAY2015.Length length=3 format=$3. AS Length,
BB_MAY2015.'DP Aired'n length=2 format=$2. AS 'DP Aired'n,
BB_MAY2015.'Daypart Aired'n length=30 format=$30. AS 'Daypart Aired'n,
BB_MAY2015.'Tape Aired'n length=13 format=$13. AS 'Tape Aired'n,
BB_MAY2015.'Tape Name Aired'n length=60 format=$60. AS 'Tape Name Aired'n,
BB_MAY2015.'Program Aired'n length=30 format=$30. AS 'Program Aired'n,
BB_MAY2015.'Date Aired'n length=8 format=MMDDYY10. AS 'Date Aired'n,
BB_MAY2015.'Time Aired'n length=6 format=$6. AS 'Time Aired'n,
BB_MAY2015.'URL Address'n length=100 format=$100. AS 'URL Address'n,
BB_MAY2015.'$ SPENT'n length=8 format=DOLLAR15.2 AS '$ SPENT'n,
BB_MAY2015.'Cl. Spots'n length=8 format=COMMA15. AS 'Cl. Spots'n,
BB_MAY2015.'W25-54RTG'n length=8 format=COMMA15.2 AS 'W25-54RTG'n,
BB_MAY2015.'W25-54IMP'n length=8 format=COMMA15.2 AS 'W25-54IMP'n,
BB_MAY2015.Coupons length=8 format=COMMA15. AS Coupons,
BB_MAY2015.'VIS To BLU'n length=8 format=COMMA15. AS 'VIS To BLU'n,
BB_MAY2015.Test length=8 format=COMMA15. AS Test,
BB_MAY2015.Store length=8 format=COMMA15. AS Store,
BB_MAY2015.'Buy Now'n length=8 format=COMMA15. AS 'Buy Now'n,
BB_MAY2015.'Planned / Actual'n length=7 format=$7. AS 'Planned / Actual'n,
BB_MAY2015.'Product _1'n length=15 format=$15. AS 'Product _1'n,
BB_MAY2015.'Reporting Market'n length=21 format=$21. AS 'Reporting Market'n,
BB_MAY2015.Country length=6 format=$6. AS Country,
BB_MAY2015.'Reporting Station'n length=27 format=$27. AS 'Reporting Station'n,
BB_MAY2015.'Reporting Daypart'n length=13 format=$13. AS 'Reporting Daypart'n,
BB_MAY2015.'Planned Spend'n length=8 format=DOLLAR15. AS 'Planned Spend'n,
BB_MAY2015.'Actual Spend'n length=8 format=DOLLAR15. AS 'Actual Spend'n,
BB_MAY2015.'Planned GRPs'n length=8 format=BEST. AS 'Planned GRPs'n,
BB_MAY2015.'Actual GRPs'n length=8 format=BEST. AS 'Actual GRPs'n,
BB_MAY2015.'Planned IMPs'n length=8 format=BEST. AS 'Planned IMPs'n,
BB_MAY2015.'Actual IMPs'n length=8 format=BEST. AS 'Actual IMPs'n,
BB_MAY2015.URL length=23 format=$23. AS URL,
BB_MAY2015.'Creative Aired'n length=66 format=$66. AS 'Creative Aired'n,
BB_MAY2015.'Unit Length'n length=3 format=$3. AS 'Unit Length'n,
BB_MAY2015.Quarter length=2 format=$2. AS Quarter,
BB_MAY2015.AO length=1 format=$1. AS AO,
BB_MAY2015.AP length=1 format=$1. AS AP,
BB_MAY2015.AQ length=1 format=$1. AS AQ
FROM
LASRLIB.BB_MAY2015 BB_MAY2015;
quit;

/** POSTPROCESSING CODE **/
proc sql;
create view weekly_sumry as
select country,
product,
'week of'n as week,

/*sum( '$ SPENT'n) as sum_pln_spent, */
sum('Actual GRPs'n) as sum_actual_grp ,
sum('Actual IMPs'n) as sum_actual_imp ,
sum('Actual Spend'n) as sum_actual_spend ,
sum('Buy Now'n) as sum_buynow,
/*sum('Cl. Spots'n) as sum_clspot,*/
/*sum(Coupons),*/
/*sum(Date Aired),*/
sum('Planned GRPs'n) as sum_planned_grp ,
sum('Planned IMPs'n) as sum_pln_imp ,
sum('Planned Spend'n) as sum_pln_spend ,
/*sum(Store),*/
/*sum(Test) ,*/
sum('VIS To BLU'n) as sum_visits,
(calculated sum_actual_spend)/(calculated sum_pln_spend) as spend_clearance,
(calculated sum_actual_imp)/(calculated sum_pln_imp) as imp_clearance,
(calculated sum_actual_spend)/( calculated sum_visits) as cpv
/*sum(W25-54IMP),*/
/*sum(W25-54RTG)*/
from TEMP_LASR_VIEW_0
group by country,product,'Week Of'n

;
quit;
proc transpose data= weekly_sumry out=weekly_sumry_tr (rename=(_name_=metric)) prefix=weekstarting;
by country product;
id week;
run;
/* Drop existing table */
%vdb_dt(LASRLIB.LSR_BBMAY2015_MTHSMRY_TR);
data LASRLIB.LSR_BBMAY2015_MTHSMRY_TR ( );
set weekly_sumry_tr ( );
run;

 

The code ran fine without any errors but when I open up the new LASR table in SAS EG, I get a blank dataset with column numbers but no data - which sounds so familiar with my earlier issue. 

 

I was able to resolve the earlier issue by recreating the dataset, re-registering it in SAS MC and re-copying into LASR library. But this is different because I am creating a sas table while transposing which LASR lib doesnt like. 

Post a Question
Discussion Stats
  • 0 replies
  • 466 views
  • 0 likes
  • 1 in conversation