conversion of code in eg to stp not working

saspert — Wed, 12 Aug 2015 14:16:32 GMT

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

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

Re: conversion of code in eg to stp not working

Sonywell — Wed, 12 Aug 2015 14:43:01 GMT

I believe the stored process session is different than the current EG session, even when executing from within EG.

Try directing it to a library other than WORK.

Re: conversion of code in eg to stp not working

saspert — Wed, 12 Aug 2015 20:07:40 GMT

I tried another library. Same issue. My manager is able to see the datasets when he runs the stored process but I cannot.

topic Re: conversion of code in eg to stp not working in Developers

conversion of code in eg to stp not working

Re: conversion of code in eg to stp not working

Re: conversion of code in eg to stp not working