I want to create a prompt for a stored process in Enterprise Guide 4.2 to be displayed in SAS Information Delivery Portal from a subset of a table. I have created a prompt in a stored process from a whole table, which is quite easy, but not what I need. I tried using a view with the following code and wanted to base the prompt off this subset - View program follows:
libname OPSPROJ meta library="OPSPROJ" METAOUT=DATA;
libname MMS meta library="MMS";
Create view OPSPROJ.Active_User_view as
SELECT Case (NameMI)
when ('') then trim(NameFirst) || ' ' || trim(NameLast)
else trim(NameFirst) || ' ' || trim(NameMI) || ' ' || trim(NameLast)
end as User_Name format=$varchar48.
, loginname as User_id
FROM MMS.SECURITY sec
Where Status = 'ACTIVE'
order by 1
here's the log:
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='MMS_Active_User_view';
4 %LET _CLIENTPROJECTPATH='D:\OPSPROJ\Q_Activity_Project_New.egp';
5 %LET _CLIENTPROJECTNAME='Q_Activity_Project_New.egp';
6 %LET _SASPROGRAMFILE=;
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
NOTE: Procedures may not support all options or statements for all devices. For details, see
the documentation for each procedure.
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis
12 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/SharedFiles/BIClientStyles/4.2/Analys
12 ! is.css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8
12 ! options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
14 GOPTIONS ACCESSIBLE;
16 libname OPSPROJ meta library="OPSPROJ" METAOUT=DATA;
NOTE: Libref OPSPROJ refers to the same library metadata as libref .
NOTE: Libref OPSPROJ was successfully assigned as follows:
Physical Name: D:\OPSPROJ
17 libname MMS meta library="MMS";
NOTE: Libref MMS was successfully assigned as follows:
Physical Name: MMS
20 proc sql;
21 Create view OPSPROJ.Active_User_view as
22 SELECT Case (NameMI)
23 when ('') then trim(NameFirst) || ' ' || trim(NameLast)
24 else trim(NameFirst) || ' ' || trim(NameMI) || ' ' ||
24 ! trim(NameLast)
25 end as User_Name format=$varchar48.
26 , loginname as User_id
27 FROM MMS.SECURITY sec
28 Where Status = 'ACTIVE'
29 order by 1
NOTE: SQL view OPSPROJ.ACTIVE_USER_VIEW has been defined.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
33 GOPTIONS NOACCESSIBLE;
2 The SAS System 10:16 Thursday, August 19, 2010
The view was successfully created, but I had a problem when I tried to register the view as a table in SAS Management Console. The libname was not defined for the security table. Maybe that's not what I should be doing? I need to use the prompt because I already have several other prompts for this stored process.
I am very new to EG but have lots of coding experience... Any ideas?