BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

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

2 REPLIES 2
Sonywell
Fluorite | Level 6

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.

saspert
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1203 views
  • 0 likes
  • 2 in conversation