topic Re: Setup multiple variables in stored process in SAS Programming

Setup multiple variables in stored process

BonnaryW — Thu, 22 Dec 2016 15:21:36 GMT

Hello,

I am stucked on how to setup a stored process to allow an Excell user to pick from the list. Thank you.

%stpbegin;

/* End EG generated code (do not edit this line) */

******************************************************************************************;

LIBNAME E '/sasdata';

DATA RPTO;

SET E.data;

IF MGMTLVL > ' ' ;

KEEP NAME MGR DBFN DBLN DBE;

PROC SORT; BY MGR ;

DATA ALLTEMPS;

LENGTH CLASS $20;

LENGTH UNTYPE $20;

SET E.data;

IF CODE='R' THEN DELETE;

KEEP NAME MGR CLASS ADDRES1 ADDRE2;

PROC SORT; BY MGR;

DATA MALL;

MERGE RPTO ALLTEMPS; BY MGRNO;

IF CLASS= ' ' || LEGNAME =' ' THEN DELETE;

PROC SORT; BY EMPNO;

%MACRO REPORTS;

%global status;

%IF &status = "B" %THEN %DO;

DATA AMALL; SET MALL;

IF CODE='B' THEN CLASS='B';

PROC SORT; BY NAME;

PROC PRINT, ID NAME;

VAR MGR CLASS ADDRES1 ADDRES2 DBFN DBLN DBE;

TITLE3 "B LIST";

%END;

%ELSE %IF &status = "C" %THEN %DO;

DATA NBTEMPS; SET MALL;

IF CODE='C' THEN CLASS='C';

PROC SORT; BY NAME;

PROC PRINT, ID NAME;

VAR MGR CLASS ADDRES1 ADDRES2 DBFN DBLN DBE;

TITLE3 "C LIST";

%END;

%ELSE %IF &status = "D" %THEN %DO;

DATA BARGTEMPS; SET MALL;

IF CODE='D' THEN CLASS='D';

PROC SORT; BY NAME;

PROC PRINT, ID NAME;

VAR MGR CLASS ADDRES1 ADDRES2 DBFN DBLN DBE;

TITLE3 "D LIST";

%END;

%MEND reports;

RUN;

%STPEND;

Re: Setup multiple variables in stored process

Cynthia_sas — Thu, 22 Dec 2016 19:34:09 GMT

Hi:
You need to use the prompting framework (assuming you are using the SAS Add-in for Microsoft Office), then if you design and define the stored process prompts correctly, when the Excel user runs the stored process, the prompting framework will put up a dialogue box with the drop down list (or the widget you choose) for the user to select from.

You'll have to look in the Stored Process Developer's Guide for more help. The Base SAS Programming forum is probably not the place for this post. There is a separate forum for Stored Processes under the Business Intelligence area.

cynthia

Re: Setup multiple variables in stored process

BonnaryW — Thu, 22 Dec 2016 22:52:23 GMT

Thank you for the repsone and yes I am using the SAS add-in for Microsoft. The old program was working fine until I added the merge data step.

Re: Setup multiple variables in stored process

Cynthia_sas — Thu, 22 Dec 2016 23:31:00 GMT

Hi:
Your code does not make sense. You have a MACRO definition inside the MERGE step, which is not typically how you define a Macro program. And, since you are defining the Macro program %REPORTS, I would expect you to have an invocation of %REPORTS someplace in your stored process. But I don't see that. The basic way to use Macro processing is to start with a working program and then Macro-tize the program and make a macro definition.

Here's an older paper that outlines the process: http://www2.sas.com/proceedings/forum2008/024-2008.pdf --this was before some of the newer capabilities of the prompting framework, but the basic process is still the same. Look at the example on page 7, which shows the macro definition appearing first and then the Macro program is invoked inside the %stpbegin/%stpend.

cynthia