BookmarkSubscribeRSS Feed
BonnaryW
Obsidian | Level 7

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;

 

 

3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
BonnaryW
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ

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

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
  • 3 replies
  • 709 views
  • 0 likes
  • 2 in conversation