topic Re: pass %let from excel (VBA) in Microsoft Integration with SAS

pass %let from excel (VBA)

YoungMin — Fri, 06 Sep 2013 03:19:41 GMT

is it possible to pass %let to an existing EGP from VBA? here is an example of what I'm trying to do:

in VBA:

dim app

dim project

dim prjNM 'the SAS EG project

set app = createobject("SASEGObjectModel.Application.4.3")

prjNM = "c:\text.egp"

set project = app.open(prjNM, "")

'add code that allows you to pass %let passme=10 in the SAS code of prjNM

a sample code in prjNM

libname dat 'sasdata\yada';

proc sql (outobs=[pass the %let passme here; &passme]);

create table data as

select *

from dat.data;

quit;

thanks in advance!

Re: pass %let from excel (VBA)

LinusH — Fri, 06 Sep 2013 08:02:39 GMT

Why are you calling an EG project?

If you have a remote SAS server, it seems simpler to deploy the project as a Stored Process, and have %let be prompt instead.

If you have a local SAS server, export the project as a SAS program file, and the call it in the background. Then you can pass any parameters to SAS using the SYSPARM SAS command line option.

Re: pass %let from excel (VBA)

YoungMin — Fri, 06 Sep 2013 14:49:33 GMT

thanks for the response linush!

1. is there a reason not to use EG? i have no preference; this is an existing EG i've inherited. the data from the EG is copied into an excel dashboard. i want to automate this so that the users who have access to the excel dashboard can refresh the data. users who want this information, want it periodically and are not SAS people either.

2. i haven't tried this before. i'll can look into it.