DATA Step, Macro, Functions and more

Connecting SAS and Excel

Reply
New User
Posts: 1

Connecting SAS and Excel

Hi, I am new to SAS. I need to pass two numeric values from excel cells to a SAS macro. But I have no idea, how to start. Right now, I am a lost ship in an ocean. Can anyone please help.

Contributor
Posts: 46

Re: Connecting SAS and Excel

I have written the code to import the data from excel into the WORK libraray. Then looping trough each observation , i'm calling from the required macro. Thus passing values from excel cells to a SAS macro.

 

PROC IMPORT OUT= WORK.EXCELTABLE DATAFILE= "C:\file.xlsx" 
     DBMS=xlsx REPLACE;
     SHEET="auto"; 
     GETNAMES=YES;
RUN;

data want;
   set EXCELTABLE;
   array COL1 COL2 COL3 ;
		do over COL1;
			%<macro name>(COL1, COL2, COL3);
		end;
   drop COL1;
run;
 

 

-Hope it helps.

Super User
Posts: 7,844

Re: Connecting SAS and Excel

Posted in reply to ankit___gupta

Looks like the typical misunderstanding of the macro processor. The macro is executed while the data step is being compiled, so it never has access to the values of datastep variables, only to their names as supplied as macro parameters.

To verify this, post the macro code.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,583

Re: Connecting SAS and Excel

Posted in reply to ankit___gupta

@ankit___gupta you missd a step in your answer: 

where is the macro definition and execution (doesn't matter what name you gave to it).

The proc import itself doesn't create macro variables.

 

the missed step, in between the import and the step to use is should be like:

 

data _NULL_;

  set have; /* dataset created by import */

        call symput('macro_var1', col1) ;  /* when col1 is char */

        call symput(macro_var2', left(col2)); /* when col2 is numeric */

run;

 

then to use the macro variables:

 

data x;

      col1 = symget('macro_var1'); 

      put col1=;

 

     /* or */

     col2 = &macro_var2; 

     put col2=;

 

    /* both metthods are good either fo char or for numeric variables */

run;

Super User
Posts: 7,844

Re: Connecting SAS and Excel


Ris124 wrote:

Hi, I am new to SAS. I need to pass two numeric values from excel cells to a SAS macro. But I have no idea, how to start. Right now, I am a lost ship in an ocean. Can anyone please help.



Step 1

Save your spreadsheet to a csv file. This makes the transfer more controllable.

Step 2

Read the csv data into SAS with a data step. Don't use proc import, as you want reliable, repeatable results.

Step 3

In a data _null_ step, use the imported dataset, use firstobs= and obs= to locate the correct row/observation (if there's more than one), and use call symput() to create the macro variables.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Super FREQ
Posts: 8,868

Re: Connecting SAS and Excel

Posted in reply to KurtBremser
...And for a basic review of SAS Macro processing, please refer to this paper: https://support.sas.com/resources/papers/proceedings13/120-2013.pdf

cynthia
Super User
Super User
Posts: 7,979

Re: Connecting SAS and Excel

Out of interest, why do you have two data elements in a spreadsheet?  What is the process you are trying to do? 

Personally, without any further information I would drop the spreadsheet totally and just have two lines at the start of the code:

%let mval1=...;

%let mval2=...;

 

Thats the only bit which needs changing then.  Save all the faffing about.

Ask a Question
Discussion stats
  • 6 replies
  • 262 views
  • 4 likes
  • 6 in conversation