Your SAS programs, embedded in web apps and elsewhere

conversion of code in eg to stp not working

Reply
Super Contributor
Posts: 275

conversion of code in eg to stp not working

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

Occasional Contributor
Posts: 13

Re: conversion of code in eg to stp not working

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.

Super Contributor
Posts: 275

Re: conversion of code in eg to stp not working

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

Ask a Question
Discussion stats
  • 2 replies
  • 522 views
  • 0 likes
  • 2 in conversation