Connecting SAS and Excel

[Ris124]

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.

[ankit___gupta]

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.

[Kurt_Bremser]

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.

[Shmuel]

@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;

[Kurt_Bremser]

---

@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.

[Cynthia_sas]

...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

[RW9]

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.