Macro not picking up numeric value from table

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Macro not picking up numeric value from table

Hi all,

 

I am trying to get values from a numeric column Start from a table in my macro. But it is reading it as a character string and not numeric. Can someone please advice what might be the problem?

 

%macro run_me;
data want;
set have;
%let x = Start;  /*numeric column from another table*/
Dif = Value_16-Value_&x;
%do i = &x+1 %to 15;
Value_&i = Value_x + (((Dif)/(16-&x))*(&i-&x));
%end;
run;
%mend;
%run_me; 

 

Thanks!


Accepted Solutions
Solution
‎12-02-2016 01:20 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Macro not picking up numeric value from table

Ok, that wasn't clear from yuor post.  So all items from start+1 to 15 need filling, so add a loop in:

data one;
infile datalines missover;
input column_1 column_2 column_3 column_4 column_5 column_6 column_7 column_8 column_9 column_10 column_11 column_12 column_13 column_14 column_15 column_16 Start;
datalines;
. . . . . . . . . 52.5 . . . . . 68.10 10
run;

data want;
  set one;
  array column_{16};
  do i=start+1 to 15; 
    column_{i}=column_{i-1} + (((column_{16} - column_{i-1}) / (16-(i-1))*(((i-1)+1)-(i-1))));
  end;
run;

Am leaving for the day now.

View solution in original post


All Replies
Super User
Super User
Posts: 6,363

Re: Macro not picking up numeric value from table

[ Edited ]

So if there is a variable named START in another dataset and you want to use the value in this data set there is no need to use macro varaibles.

If dataset HAVE has a set of variables name VALUE_1 to VALUE_16 then you can use an ARRAY to reference them in your DATA step.

Since a macro variable can only hold one value I assume that the OTHER dataset just has one value of START that you want to use.

 

data want;
  set have;
  if _n_=1 then set OTHER(keep=start);
  array value_ (16) ;
  dif = value_(16) - value_(start);
  do i=start+1 to 15 ;
    Value_(i) = Value_(start) + (((Dif)/(16-start)*(i-start));
  end;
run;

 

If instead there are mulitple values of START then perhaps you want to generate a new dataset that has the combinations you want and then apply your calculations in another data step.

proc sql ;
  create table MIDDLE as
   select a.*,b.start 
   from have a , OTHER b
  ;
quit;
data want ;
   set middle ;
   .... apply calculations ... 
run;

Or perhaps you can just use a data step to make the combination of the two tables.

data want ;
  set OTHER ;
  do p=1 to nobs ;
    set have nobs=nobs point=p ;
    ... make calculations here ....
    output;
  end;
run;
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Macro not picking up numeric value from table

Sorry, that is a lack of understanding in what macro is and how to use it.  Macro language is a pre-processor (i.e. it is not executable code) tool to generate text - a bit like Find/Replace functionality.  It does not by itself do anything and it is not Base SAS code like datasteps and such like.  So, in your code you have a macro do loop

%do i = &x+1 %to 15;
Now replace &x with the value you assigned to it:

%do i = start+1 %to 15;

 

That is not a valid macro sentance, start is a variable in a dataset in Base SAS, not in macro, the two are separate entities.

 

TBH though I would question what you are doing here at all.  You have a wide dataset yes - i.e. variables going across - which is where your problem lies.  You now to work round that by creating macro code.  Neither of these you need, first thing I would do is normalise the data so the variables go down rather than across.  If you have to use the data going across, then the method to access them in Base SAS is to use arrays:

data want;
  set have;
  array value_{10};
  do i=1 to 10;
    ...;
  end;
run;

I can't see exactly what your doing as you have provided no test data (in the form of a datastep) or required output, so the above is an example.  

Contributor
Posts: 40

Re: Macro not picking up numeric value from table

Hi RW9,

 

I have a column 'start' from a data set, which I am trying to access in the macro. for each do iteration, I want the value from Start to be plugged in the macro and computation to happen accordingly. But in %let x = Start; it is treating Start as a character string instead of picking up value from the Start column. I want to how I can reference the Start column and assign its value to macro variable x.

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Macro not picking up numeric value from table

[ Edited ]

Sorry, please re-read my post.  You do not mix datastep and macro in that way.  You use Base SAS to manipulate data.  You use Macro language to generate text Base SAS code, the two are very different.

 

Particularly:

"as you have provided no test data (in the form of a datastep) or required output,"

 

If you provide some test data in the form of a datastep, and what you want the output to look like, I can provide code.  Your code as is will not work.

Contributor
Posts: 40

Re: Macro not picking up numeric value from table

[ Edited ]

Hi RW9,

this is how my data looks like.

 

data one;
infile datalines missover;
input column_1 column_2 column_3 column_4 column_5 column_6 column_7 column_8 column_9 column_10 column_11 column_12 column_13 column_14 column_15 column_16 $ Start;

datalines;
. . . . . . . . . 52.5 . . . . . 68.10 10
run;

 

I want to interpolate values for columns column_11 to column_15 (the missing values) based on formula

column_11 = column_10 + (((Column_16 - Column_10)/(16-10))*(11-10)); 

 

The Start denotes the column_name from which the interpolation should start. It will always have a value like column_16.

 

Thanks!

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Macro not picking up numeric value from table

Ok, am not sure about your calculation, but I can generalise the formula based off the start variable and guessing your logic - note that in your test data column_16 is text for some reason - it should be numeric:

data one;
infile datalines missover;
input column_1 column_2 column_3 column_4 column_5 column_6 column_7 column_8 column_9 column_10 column_11 column_12 column_13 column_14 column_15 column_16 Start;
datalines;
. . . . . . . . . 52.5 . . . . . 68.10 10
run;

data want;
  set one;
  array column_{16};
  column_{start+1}=column_{start} + (((column_{16} - column_{start}) / (16-start)*((start+1)-start)));
run;
Contributor
Posts: 40

Re: Macro not picking up numeric value from table

Hi RW9,

 

it calculated value only for column_11. And not for columns column_12 to column_15.

Solution
‎12-02-2016 01:20 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Macro not picking up numeric value from table

Ok, that wasn't clear from yuor post.  So all items from start+1 to 15 need filling, so add a loop in:

data one;
infile datalines missover;
input column_1 column_2 column_3 column_4 column_5 column_6 column_7 column_8 column_9 column_10 column_11 column_12 column_13 column_14 column_15 column_16 Start;
datalines;
. . . . . . . . . 52.5 . . . . . 68.10 10
run;

data want;
  set one;
  array column_{16};
  do i=start+1 to 15; 
    column_{i}=column_{i-1} + (((column_{16} - column_{i-1}) / (16-(i-1))*(((i-1)+1)-(i-1))));
  end;
run;

Am leaving for the day now.

Contributor
Posts: 40

Re: Macro not picking up numeric value from table

Thank you so much RW9!

Grand Advisor
Posts: 10,215

Re: Macro not picking up numeric value from table

Generic help in addressing code: Provide a brief example of the input data set(s) and an example of the desired output for that example. And a description of the logic needed between the two.

 


akhilesh_joshi wrote:

Hi RW9,

 

I have a column 'start' from a data set, which I am trying to access in the macro. for each do iteration, I want the value from Start to be plugged in the macro and computation to happen accordingly. But in %let x = Start; it is treating Start as a character string instead of picking up value from the Start column. I want to how I can reference the Start column and assign its value to macro variable x.


ALL values in ANY variable created as a Macro variable are TEXT. Always. You may be able to use them in the text code generated by the macro processor as numeric literal values but the actual macro is text. So &x contains the NAME of the variable START, not the value.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 288 views
  • 0 likes
  • 4 in conversation