I recieved the following bit of code from SAS support. We are trying to do similiar things with added flexibility. I'm having trouble assigning varialbe names to dynamically created text boxes so I can append/ update back the values to a SAS table. also checkout Joe Flynn's paper here -> https://support.sas.com/resources/papers/proceedings09/330-2009.pdf
* Begin EG generated code (do not edit this line); * * Stored process registered by * Enterprise Guide Stored Process Manager V6.1 * * ==================================================================== * Stored process name: test_webout * ==================================================================== *; *ProcessBody; %STPBEGIN; * End EG generated code (do not edit this line); /* --- Start of code for "Test Stored Process". --- */ /* add_update_delete_report.sas This program permits you to add, */ /* update and delete observations from a SAS data set and to */ /* produce a report. */ /*END*/ /******************************************************************/ /* */ /* Name: add_update_delete_report_stp.sas */ /* */ /* Purpose : Data Entry and Update Sample program. */ /* This program illustrates an approach to add, update, */ /* and delete observations in a SAS data set. */ /* */ /* NOTE: you must modify the LIBNAME MYLIB statement below. */ /* */ /* Create a stored process using this code. */ /* Then, submit a request via your Web browser to execute it. */ /* */ /******************************************************************/ /* You must modify the following LIBNAME statement to reference */ /* your own site-specific SAS library. */ libname mylib "/apps/sas/datasets/data15/MAHIMS/Data"; %global reqtype empno name address phoneno ; %macro menu1; data _null_; file _webout; thissrv = symget('_url'); thispgm = symget('_program'); put "
"; put "
Sample Employee Database Application
"; put 'Add New Employee
'; put 'Update Employee Information
'; put 'Delete an Employee
'; put 'Report of all Employees
'; put "
"; run; %mend menu1; %macro return_link; data _null_; file _webout; thissrv = symget('_url'); thispgm = symget('_program'); put "
"; put "
"; put 'Return to Main Menu
'; put ''; put '
'; run; %mend return_link; %macro add_form; /* Output data entry form */ data _null_; file _webout; thissrv = symget('_url'); thispgm = symget('_program'); put '
'; put ''; put '
Add New Employee Information
'; put '
'; run; %return_link; %mend add_form; %macro addrec; /* Update existing data set */ proc sql; create table work.results as select * from mylib.employee where empno = &empno; run; data _null_; /* Check if empno already exists */ if (count=0) then do; call symput('exists','no'); end; else do; call symput('exists','yes'); end; stop; set results nobs=count; run; %macro add_if_new; %if ("&exists" eq "no") %then %do; proc sql; insert into mylib.employee set empno=&empno, name = "&name", phoneno="&phoneno", address="&address"; quit; data _null_; file _webout; put "Employee &name was added.
"; run; %end; %else %do; data _null_; file _webout; put "
Sorry, Employee &empno already exists.
"; run; %end; %mend add_if_new; %add_if_new; %return_link; %mend addrec; %macro delete_it; /* Delete an employee */ proc sql; create table work.results as select * from mylib.employee where empno = &empno; run; data _null_; set results; call symput('name',name); run; proc sql; delete from mylib.employee where empno=&empno; quit; data _null_; file _webout; if "&name" ne "" then do; put "Employee Number &empno - &name was deleted.
"; end; else do; put "Sorry, Employee Number &empno was not found.
"; end; run; %return_link; %mend delete_it; %macro report; ods listing close; ods html body=_webout rs=none; proc sort data=mylib.employee; by empno; run; title "Employee Database"; footnote1 " "; proc print data=mylib.employee; run; ods html close; %return_link; %mend report; %macro update_form; data _null_; file _webout; thissrv = symget('_url'); thispgm = symget('_program'); put ''; put ''; put '
Update Employee Information
'; put '
'; run; %return_link; %mend update_form; %macro delete_form; data _null_; file _webout; thissrv = symget('_url'); thispgm = symget('_program'); put ''; put ''; put '
Delete Employee
'; put '
'; run; %return_link; %mend delete_form; %macro update; proc sql; create table work.results as select * from mylib.employee where empno = &empno; run; data _null_; if (count = 0) then do; file _webout; put ""; put "Sorry, employee &empno does not exist
"; put ""; end; set results nobs=count; file _webout; thissrv = symget('_url'); thispgm = symget('_program'); put ''; put ''; put '
Update Employee Information
'; put '
'; put ''; put ''; stop; run; %return_link; %mend update; %macro update_it; proc sql; update mylib.employee set name = "&name", phoneno="&phoneno", address="&address" where empno=&empno; quit; data _null_; file _webout; put "Employee &empno was updated.
"; run; %return_link; %mend update_it; %macro ifexist(dsname); /* If the data set does not exist, then create it. */ /* This step is needed only when this demo is set up */ /* initially. */ %local dsname; %if %sysfunc(exist(&dsname)) %then; %else %do; %put it does not exist; data &dsname; format name $char25.; format empno 8.; format address $char40.; format phoneno $char15.; stop; run; %end; %mend ifexist; %macro main; %if "&reqtype" eq "add" %then %do; %addrec; %end; %else %if "&reqtype" eq "addform" %then %do; %add_form; %end; %else %if "&reqtype" eq "report" %then %do; %report; %end; %else %if "&reqtype" eq "updform" %then %do; %update_form; %end; %else %if "&reqtype" eq "update" %then %do; %update; %end; %else %if "&reqtype" eq "update2" %then %do; %update_it; %end; %else %if "&reqtype" eq "delete" %then %do; %delete_form; %end; %else %if "&reqtype" eq "delete2" %then %do; %delete_it; %end; %else %do; %menu1; %end; %mend main; %ifexist(mylib.employee); /* This step is needed only for setup */ %main; /* --- End of code for "Test Stored Process". --- */ * Begin EG generated code (do not edit this line); ;*';*";*/;quit; %STPEND; * End EG generated code (do not edit this line);
