BookmarkSubscribeRSS Feed
Ris124
Calcite | Level 5

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.

6 REPLIES 6
ankit___gupta
Quartz | Level 8

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
Super User

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
Garnet | Level 18

@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
Super User

@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
SAS Super FREQ
...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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1146 views
  • 4 likes
  • 6 in conversation