Hi,
I just converted the below code into a stored process. When I try to run it, I get a log with no errors but I dont see the final table in the work lib.
%stpbegin;
libname WWTest odbc complete="driver=SQL Server; user=SasDatauser; pwd=Horizon@75; database=WeightWatchers; Server=HMIDWPRD03" schema=dbo;
PROC SQL;
CREATE TABLE WORK.VW_DASH_DISPLAY_ext AS
SELECT t1.CampaignName,
t1.Date,
t1.WeekOf,
t1.Partner,
t1.Spend,
t1.Impressions,
t1.Clicks,
t1.WeightedSubscriptions,
t1.InDemoImpressions,
t1.ViewablityMeasuredImpression,
t1.ViewablityInViewImpression,
t1.HomePage,
t1.AllFemaleImpressions,
/* prev_week */
(put(intnx('week',datepart(t1.WeekOf),-1),mmddyy10.)) AS prev_week,
/* currweek */
(put(datepart(t1.WeekOf),mmddyy10.)) AS currweek
FROM WWTEST.vw_Dash_Display t1;
QUIT;
PROC SQL;
CREATE TABLE WORK.VW_DASH_DISPLAY1 AS
SELECT DISTINCT t1.currweek,
/* SUM_of_Impressions */
(SUM(t1.Impressions)) FORMAT=20. AS SUM_of_Impressions,
/* SUM_of_Clicks */
(SUM(t1.Clicks)) FORMAT=20. AS SUM_of_Clicks,
/* SUM_of_WeightedSubscriptions */
(SUM(t1.WeightedSubscriptions)) FORMAT=20.4 AS SUM_of_WeightedSubscriptions,
/* SUM_of_InDemoImpressions */
(SUM(t1.InDemoImpressions)) FORMAT=20. AS SUM_of_InDemoImpressions,
/* SUM_of_ViewablityMeasuredImpress */
(SUM(t1.ViewablityMeasuredImpression)) FORMAT=20.4 AS SUM_of_ViewablityMeasuredImpress,
/* SUM_of_ViewablityInViewImpressio */
(SUM(t1.ViewablityInViewImpression)) FORMAT=20.4 AS SUM_of_ViewablityInViewImpressio,
/* SUM_of_HomePage */
(SUM(t1.HomePage)) FORMAT=20. AS SUM_of_HomePage,
/* SUM_of_AllFemaleImpressions */
(SUM(t1.AllFemaleImpressions)) FORMAT=20. AS SUM_of_AllFemaleImpressions
FROM WORK.VW_DASH_DISPLAY_ext t1
GROUP BY t1.currweek;
QUIT;
PROC SQL;
CREATE TABLE WORK.VW_DASH_DISPLAY2 AS
SELECT DISTINCT t1.prev_week,
/* SUM_of_Impressions */
(SUM(t1.Impressions)) FORMAT=20. AS SUM_of_Impressions,
/* SUM_of_Clicks */
(SUM(t1.Clicks)) FORMAT=20. AS SUM_of_Clicks,
/* SUM_of_WeightedSubscriptions */
(SUM(t1.WeightedSubscriptions)) FORMAT=20.4 AS SUM_of_WeightedSubscriptions,
/* SUM_of_InDemoImpressions */
(SUM(t1.InDemoImpressions)) FORMAT=20. AS SUM_of_InDemoImpressions,
/* SUM_of_ViewablityMeasuredImpress */
(SUM(t1.ViewablityMeasuredImpression)) FORMAT=20.4 AS SUM_of_ViewablityMeasuredImpress,
/* SUM_of_ViewablityInViewImpressio */
(SUM(t1.ViewablityInViewImpression)) FORMAT=20.4 AS SUM_of_ViewablityInViewImpressio,
/* SUM_of_HomePage */
(SUM(t1.HomePage)) FORMAT=20. AS SUM_of_HomePage,
/* SUM_of_AllFemaleImpressions */
(SUM(t1.AllFemaleImpressions)) FORMAT=20. AS SUM_of_AllFemaleImpressions
FROM WORK.VW_DASH_DISPLAY_ext t1
GROUP BY t1.prev_week;
QUIT;
PROC SQL;
CREATE TABLE work.wwdisp_pctdiff_prevweek AS
SELECT t1.currweek,
t1.SUM_of_Impressions,
t1.SUM_of_Clicks,
t1.SUM_of_WeightedSubscriptions,
t1.SUM_of_InDemoImpressions,
t1.SUM_of_ViewablityMeasuredImpress,
t1.SUM_of_ViewablityInViewImpressio,
t1.SUM_of_HomePage,
t1.SUM_of_AllFemaleImpressions,
t2.SUM_of_Impressions AS SUM_of_Impressions1,
t2.SUM_of_Clicks AS SUM_of_Clicks1,
t2.SUM_of_WeightedSubscriptions AS SUM_of_WeightedSubscriptions1,
t2.SUM_of_InDemoImpressions AS SUM_of_InDemoImpressions1,
t2.SUM_of_ViewablityMeasuredImpress AS SUM_of_ViewablityMeasuredImpres1,
t2.SUM_of_ViewablityInViewImpressio AS SUM_of_ViewablityInViewImpressi1,
t2.SUM_of_HomePage AS SUM_of_HomePage1,
t2.SUM_of_AllFemaleImpressions AS SUM_of_AllFemaleImpressions1,
/* pct_diff_imp_ovr_previous_week */
((t1.SUM_of_Impressions-t2.SUM_of_Impressions)/t2.SUM_of_Impressions) AS pct_diff_imp_ovr_previous_week,
/* pctdiff_clicks_overprevweek */
((t1.SUM_of_Clicks-t2.SUM_of_Clicks)/t2.SUM_of_Clicks) AS pctdiff_clicks_overprevweek,
/* pctdiff_w_subs */
((t1.SUM_of_WeightedSubscriptions-t2.SUM_of_WeightedSubscriptions)/t2.SUM_of_WeightedSubscriptions) AS
pctdiff_w_subs,
/* pctdiff_indemo_imp */
((t1.SUM_of_InDemoImpressions-t2.SUM_of_InDemoImpressions)/t2.SUM_of_WeightedSubscriptions) AS
pctdiff_indemo_imp,
/* pctdiff_v_msr_imp */
((t1.SUM_of_ViewablityMeasuredImpress-t2.SUM_of_ViewablityMeasuredImpress)/t2.SUM_of_ViewablityMeasuredImpress)
AS pctdiff_v_msr_imp,
/* pctdiff_inview_v_imp */
((t1.SUM_of_ViewablityInViewImpressio-t2.SUM_of_ViewablityInViewImpressio)/t2.SUM_of_ViewablityInViewImpressio
) AS pctdiff_inview_v_imp,
/* pctdiff_homepage */
((t1.SUM_of_HomePage-t2.SUM_of_HomePage)/t2.SUM_of_HomePage) AS pctdiff_homepage,
/* pctdiff_allfemimp */
((t1.SUM_of_AllFemaleImpressions-t2.SUM_of_AllFemaleImpressions)/t2.SUM_of_AllFemaleImpressions) AS
pctdiff_allfemimp
FROM WORK.VW_DASH_DISPLAY1 t1
LEFT JOIN WORK.VW_DASH_DISPLAY2 t2 ON (t1.currweek = t2.prev_week);
QUIT;
%stpend;
the summary is given below as well -
Descriptive information
Name
ww_stp
Location
/User Folders/RAddepalli/My Folder/weightwatchers/
Description
None
Usage Version
2.0
IsHidden
No
Keywords
None
Responsible parties
User Role
RAddepalli owner
SAS code
* Begin EG generated code (do not edit this line);
*
* Stored process registered by
* Enterprise Guide Stored Process Manager V7.1
*
* ====================================================================
* Stored process name: ww_stp
* ====================================================================
*;
*ProcessBody;
* End EG generated code (do not edit this line);
%stpbegin;
libname WWTest odbc complete="driver=SQL Server; user=SasDatauser; pwd=Horizon@75; database=WeightWatchers; Server=HMIDWPRD03" schema=dbo;
PROC SQL;
Check "Show full SAS code" to see the entire source listing
Execution server information
Source code path
None
Source file name
None
Server Type
Workspace Server Only
Store Code In Metadata
Yes
Allow Execution on Other Application Servers
Yes
Forms of results supported
■ Status
■ Package
■ Streaming
Input prompts and output parameters
Input prompts (0)
None
Output parameters (0)
None
Data sources and targets
Data sources (0)
None
Data targets (0)
None
