Move data from excel cell to SAS program

lmonteros — Tue, 12 Jun 2018 21:21:16 GMT

Hello, there.

So what I am trying to do is take values from my excel sheet to update a SAS program. My code goes like this:

ods excel;
data _null_;

%LET SS1 = '15MAY2018'd;
%LET SS2 = '05JUN2018'd;

proc sql;
create table SAS3 as
select A.PHRHIST_PAYNO
from UVICRPT.V_HRS_PAYROLL_PHRHIST A, UVICRPT.V_HRS_PAYROLL_PHRDEDN B
WHERE
AND A.PHRHIST_ACTIVITY_DATE BETWEEN &SS1 AND &SS2
AND B.PHRDEDN_ACTIVITY_DATE BETWEEN &SS1 AND &SS2
AND A.PHRHIST_PICT_CODE = B.PHRDEDN_PICT_CODE
AND A.PHRHIST_YEAR = B.PHRDEDN_YEAR
AND A.PHRHIST_SEQ_NO = B.PHRDEDN_SEQ_NO;
run;

proc print data=SAS3 style=Table noobs;
run;
ods excel close;

QUIT;

But I would like the variables SS1 and SS2 to take values from cells in my worksheet. Something like:

%LET SS1 = C5

Any ideas? I'm using the Add-in in Excel, SAS 7.15

Re: Move data from excel cell to SAS program

Kurt_Bremser — Wed, 13 Jun 2018 07:55:53 GMT

I'd try to create a stored process from the program, with both those dates supplied from user prompts. Then I'd look how to automatically send the dates to those SAS prompts via VBA.

But that would be a secondary option by a mile. My primary option would be to do it all on the proper platform, and avoid all those Excel interaction shortcomings.

topic Move data from excel cell to SAS program in Microsoft Integration with SAS

Move data from excel cell to SAS program

Re: Move data from excel cell to SAS program