Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Invoking a SAS job from Excel
- Thread starter jpw999
- Start date
Hi jpw, welcome to SAS.
It is possible to launch SAS jobs from Excel, the question is why you want to do it. Not questioning whether it needs to be done, but your aim will determine the best method. SAS does have an Excel add-in which allows you to run SAS functions etc from within Excel, however, this is an extra component you'd need to pay for, so you may not have it. I've seen it demo'd and it looks good, but I've never used it myself, so can't help you with that.
An alternative, if you want to launch a specific SAS job is to use VBA code to launch and run the SAS pogram. Again, I've never done this myself, but I regularly use DOS command scripts to submit SAS jobs, so I know that the idea is sound.
I believe that there may be a SAS component that can be added to your VBA as well, possibly allowing you to run the SAS code from Excel... I'm not sure about that though...
I hope that this helps, or at least gives you some avenues to pursue.
Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.
It is possible to launch SAS jobs from Excel, the question is why you want to do it. Not questioning whether it needs to be done, but your aim will determine the best method. SAS does have an Excel add-in which allows you to run SAS functions etc from within Excel, however, this is an extra component you'd need to pay for, so you may not have it. I've seen it demo'd and it looks good, but I've never used it myself, so can't help you with that.
An alternative, if you want to launch a specific SAS job is to use VBA code to launch and run the SAS pogram. Again, I've never done this myself, but I regularly use DOS command scripts to submit SAS jobs, so I know that the idea is sound.
I believe that there may be a SAS component that can be added to your VBA as well, possibly allowing you to run the SAS code from Excel... I'm not sure about that though...
I hope that this helps, or at least gives you some avenues to pursue.
Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.
I second Chris's question, why would you want to do this.
But I always like a challenge and since Chris mentioned that SAS sells something that does this I wondered if I could do it without their expensive add-on.
Viola! I got it to run. What you need to do is create a macro that uses the Shell() command. For the path param insert the very same 'run' line that you find on your SAS shortcut.
ex.
This should launch the SAS software. Now if you would like to run a SAS program all you have to do is use th -autoexec option in the run line. SAS will run the autoexec program as soon as its launched. If you're really clever you can even have SAS close itself when its done with the program by using the ENDSAS statement.
But I still question the need to run a SAS job from Excel.
Happy coding,
Klaz
But I always like a challenge and since Chris mentioned that SAS sells something that does this I wondered if I could do it without their expensive add-on.
Viola! I got it to run. What you need to do is create a macro that uses the Shell() command. For the path param insert the very same 'run' line that you find on your SAS shortcut.
ex.
Code:
Sub test()
t = Shell("D:\SAS9\sas.exe -CONFIG D:\SAS9\nls\en\SASV9.CFG",1)
End SubThis should launch the SAS software. Now if you would like to run a SAS program all you have to do is use th -autoexec option in the run line. SAS will run the autoexec program as soon as its launched. If you're really clever you can even have SAS close itself when its done with the program by using the ENDSAS statement.
But I still question the need to run a SAS job from Excel.
Happy coding,
Klaz
- Thread starter
- #5
Hey guys. Thanks for your input. The reason I want to test this out is because we have a few jobs that we run that are monotonous and repetitive. We would like to move them over to an admin type person to do but they won't have access to the SAS server. So what I want to do is build an Excel interface with a few dropdowns for parameters and have it kick off the sas job.
John
John
clinicalsas
Programmer
Can you list some criticality for me while anlysing SAS data sets?
- Status
Similar threads
- Locked
- Question
- Locked
- Question