topic Re: Importing one value from excel into SAS in SAS Programming

Importing one value from excel into SAS

irisG — Thu, 23 Aug 2018 08:45:04 GMT

I understand that SAS can easily import excel data and treat it as SAS data set. However, if I just need one value from excel and use that value as a SAS variable. Is it possible? How do I go about doing that? Thanks!

Re: Importing one value from excel into SAS

Jagadishkatam — Thu, 23 Aug 2018 08:47:16 GMT

You can use the same proc import procedure as below

proc import datafile='path of excel' out=sasdataset dbms=excel replace;
run;

Re: Importing one value from excel into SAS

RW9 — Thu, 23 Aug 2018 08:48:35 GMT

Of what benefit is keeping one value in an Excel file to use in SAS? There are many ways to get data into SAS. You could just type it in at the top of the program:

% let thevalue=abc;

You could have prompts etc.

Re: Importing one value from excel into SAS

Kurt_Bremser — Thu, 23 Aug 2018 09:09:28 GMT

If that value is just typed into a spreadsheet by somebody, have them type it into a SAS prompt instead and spare yourself the hassle of dealing with the Excel ****.

Re: Importing one value from excel into SAS

irisG — Thu, 23 Aug 2018 09:11:33 GMT

I am toying with automating part of the process that currently involve talking to an excel spreadsheet and reiterating the calculation. Right now we are using the same solution as you suggested but I wonder whether there are ways to improve the process.

Re: Importing one value from excel into SAS

RW9 — Thu, 23 Aug 2018 09:31:57 GMT

Well, the biggest improvement to the process would be to drop SAS or Excel. Fewer tools in the process always makes for a simpler, easier process. For instance, what is Excel needed? Can it not be stored as a SAS dataset, then output to Excel at the end of the process? What is SAS needed for, if you have an Excel file which needs updating, update it using VBA which is inbuilt into Excel. Either scenario halves the tools and probably the effort.

Re: Importing one value from excel into SAS

irisG — Thu, 23 Aug 2018 11:05:18 GMT

That's definitely a tidier solution. I am trying to fact find at this stage to see what different approaches are available. One idea was to call the SAS code from VBA but our issue is that the code is too long if I use the Jointext function to join all the code (this is what I saw as one of the solutions, by puting a code in a named cell and then execute the SAS code from VBA.)

Re: Importing one value from excel into SAS

Kurt_Bremser — Thu, 23 Aug 2018 11:13:48 GMT

What's so bad about a simple %include, if you want to run SAS code stored in a file?