topic Re: Assign a value from a table to a macro in SAS Programming

Assign a value from a table to a macro

Anita_n — Tue, 19 Sep 2023 08:21:21 GMT

Dear all,

if I have a data that looks like this:

data have;
	infile datalines;
	input ans $4. pos var1_unit1 $6.var2_unit2 $7. var3_unit3 $6.;
	datalines;
yes 1 yellow red	black
no  2 blue   white  braun
no  2 Orange green	pink 
yes 3 green	 blue   gray
yes 1 white	 purple green
;
run;

data _null_;
set have;
call symput("M", pos); 
run;

%put my value is; &M;

data want;
set have;
if ans="yes" then color=var&M._unit;
run;

I want to output the value of color using the "pos" value if the condition =yes

but am not getting it. please any help?

My result should look like this

data result;
	infile datalines;
	input ans $4. pos var1_unit1 $6.var2_unit2 $7. var3_unit3 $6. color $7.;
	datalines;
yes 1 yellow red	black yellow
no  2 blue   white  braun
no  2 Orange green	pink
yes 3 green	 blue   gray  gray
yes 1 white	 purple green white
;
run;

Re: Assign a value from a table to a macro

andreas_lds — Tue, 19 Sep 2023 08:30:27 GMT

You don't need macro code:

data want;
   set have;

   array vars[3] var1_unit1 var2_unit2 var3_unit3;

   if ans = 'yes' then Colour = vars[pos];
run;

Re: Assign a value from a table to a macro

Anita_n — Tue, 19 Sep 2023 09:09:29 GMT

@andreas_lds I get this error message running the code in the second part of the program, Any idea how to solve this. In the first part it worked

ERROR: Array subscript out of range in row15 column 97.

Re: Assign a value from a table to a macro

andreas_lds — Tue, 19 Sep 2023 09:16:27 GMT

Please post the complete log including the code.

Re: Assign a value from a table to a macro

Anita_n — Tue, 19 Sep 2023 09:30:00 GMT

@andreas_lds Is it possible to use a macro instead of the array? My code contains some patient details I can unfortunately not post it here.

That was the log message I got.

Re: Assign a value from a table to a macro

PaigeMiller — Tue, 19 Sep 2023 09:54:45 GMT

The code from @andreas_lds ought to work.

The error message

ERROR: Array subscript out of range in row15 column 97.

usually indicates that the value of the subscript (in @andreas_lds code it is variable POS) is either greater than 3 (because in his code the ARRAY had 3 elements) or it is less than 1, or missing, or not an integer. So a value of pos=4, or pos=., or pos=0 (and many other values) will cause this error to happen. Only integer values 1 or 2 or 3 will work here. So you need to look at the values of POS in your actual data, and make sure they are integers between 1 and the number of variables in the array.

No need for a macro solution here, and furthermore the macro solution will run into the same difficulties.

Re: Assign a value from a table to a macro

andreas_lds — Tue, 19 Sep 2023 10:14:55 GMT

Can you post the array statement, at least? If you can't, it will hardly possible to help you.

Re: Assign a value from a table to a macro

Anita_n — Tue, 19 Sep 2023 11:33:05 GMT

@andreas_lds @PaigeMiller

Okay let me try to explain the problem this way. I have adjusted the code to make it look like the way I have it in my main code

I hope this can help

data have;
	infile datalines;
	input ans $4. pos1 2. pos2 2. pos3 2. pos4 2. pos5 2. pos6 2.  var1_unit1 $6.var2_unit2 $7. var3_unit3 $6. 
    var4_unit4 $7.  var5_unit5 $7.  var6_unit6 $7.  var7_unit7 $6.  var8_unit8 $7.  var9_unit9 $6.  var10_unit10 $6. 
    var11_unit11 $6. var12_unit12 $7.;
	datalines;
yes 1 2 1 5 1 4 yellow red	  black yellow yellow red	black yellow yellow red	  black yellow
no  2 3 5   1 4 blue   white  braun blue   white  braun blue  white  braun  blue  white braun
no  2   4   2   Orange green  pink  blue   white  braun blue  white  braun  blue  white braun
yes 3 1   2 5   green  blue   gray  gray   Orange green pink  blue   Orange green pink  blue
yes 1 4   2 4   white  purple green white  green  blue  gray  gray   green  blue  gray  gray
;
run;

%macro want(outdat, num);
data &outdat;
   set have;

   array vars[12] var1_unit1 var2_unit2 var3_unit3 var4_unit4 var5_unit5 var6_unit6 var7_unit7 
          var8_unit8 var9_unit9 var10_unit10 var11_unit11 var12_unit12;

   if ans = 'yes' then Colour = vars[&num];
run;
%mend want;
%want(want1, pos1); %want(want2, pos2); %want(want3, pos3); %want(want4, pos4); %want(want5, pos5); %want(want6, pos6);

Thanks for the help

Re: Assign a value from a table to a macro

PaigeMiller — Tue, 19 Sep 2023 11:45:21 GMT

And what is wrong with this code? Please tell us. Do not simply show us code that doesn't work without explanation; explain. If it doesn't work and there are errors in the log, show us the entire LOG for this code. Since there are macros involved, please turn on macro debugging options first by running this line of code before the rest of your code, and then show us the log.

options mprint;

There is no customer data here, so please don't use that as an excuse.

Re: Assign a value from a table to a macro

PaigeMiller — Tue, 19 Sep 2023 11:44:45 GMT

@Anita_n wrote:

@andreas_lds @PaigeMiller

Okay let me try to explain the problem this way. I have adjusted the code to make it look like the way I have it in my main code

I hope this can help

data have;
	infile datalines;
	input ans $4. pos1 2. pos2 2. pos3 2. pos4 2. pos5 2. pos6 2.  var1_unit1 $6.var2_unit2 $7. var3_unit3 $6. 
    var4_unit4 $7.  var5_unit5 $7.  var6_unit6 $7.  var7_unit7 $6.  var8_unit8 $7.  var9_unit9 $6.  var10_unit10 $6. 
    var11_unit11 $6. var12_unit12 $7.;
	datalines;
yes 1 2 1 5 1 4 yellow red	  black yellow yellow red	black yellow yellow red	  black yellow
no  2 3 5   1 4 blue   white  braun blue   white  braun blue  white  braun  blue  white braun
no  2   4   2   Orange green  pink  blue   white  braun blue  white  braun  blue  white braun
yes 3 1   2 5   green  blue   gray  gray   Orange green pink  blue   Orange green pink  blue
yes 1 4   2 4   white  purple green white  green  blue  gray  gray   green  blue  gray  gray
;
run;

%macro want(outdat, num);
data &outdat;
   set have;

   array vars[12] var1_unit1 var2_unit2 var3_unit3 var4_unit4 var5_unit5 var6_unit6 var7_unit7 
          var8_unit8 var9_unit9 var10_unit10 var11_unit11 var12_unit12;

   if ans = 'yes' then Colour = vars[&num];
run;
%mend want;
%want(want1, pos1); %want(want2, pos2); %want(want3, pos3); %want(want4, pos4); %want(want5, pos5); %want(want6, pos6);

You cannot use macro variable &num as the argument to an array call in this cased, because the value of macro variable &num is NOT an integer between 1 and 12. What is the value of &num?? The first time you call it, the value of &num is pos1, which is not an integer.

I still contend that macros are not needed here, macros just complicate things unnecessarily. Why do you feel you need to run %WANT multiple times? Please explain. Also please note, I am not asking you to explain the code, I want to understand WHY you are doing things and what the desired output is.

Re: Assign a value from a table to a macro

Ksharp — Tue, 19 Sep 2023 11:47:44 GMT

/*Try function VVALUEX()*/

data have;
	infile datalines;
	input ans $4. pos1 2. pos2 2. pos3 2. pos4 2. pos5 2. pos6 2.  var1_unit1 $6.var2_unit2 $7. var3_unit3 $6. 
    var4_unit4 $7.  var5_unit5 $7.  var6_unit6 $7.  var7_unit7 $6.  var8_unit8 $7.  var9_unit9 $6.  var10_unit10 $6. 
    var11_unit11 $6. var12_unit12 $7.;
	datalines;
yes 1 2 1 5 1 4 yellow red	  black yellow yellow red	black yellow yellow red	  black yellow
no  2 3 5   1 4 blue   white  braun blue   white  braun blue  white  braun  blue  white braun
no  2   4   2   Orange green  pink  blue   white  braun blue  white  braun  blue  white braun
yes 3 1   2 5   green  blue   gray  gray   Orange green pink  blue   Orange green pink  blue
yes 1 4   2 4   white  purple green white  green  blue  gray  gray   green  blue  gray  gray
;
run;

data want;
 set have;
if not missing(pos1) then want1=vvaluex(cats('var',pos1,'_unit',pos1));
if not missing(pos2) then want2=vvaluex(cats('var',pos2,'_unit',pos2));
if not missing(pos3) then want3=vvaluex(cats('var',pos3,'_unit',pos3));
if not missing(pos4) then want4=vvaluex(cats('var',pos4,'_unit',pos4));
if not missing(pos5) then want5=vvaluex(cats('var',pos5,'_unit',pos5));
if not missing(pos6) then want6=vvaluex(cats('var',pos6,'_unit',pos6));
run;

Re: Assign a value from a table to a macro

Kurt_Bremser — Tue, 19 Sep 2023 13:11:23 GMT

You do have missing values in your posX variables, which can't be used as index to an array. Your code must provide for this situation. Assign colour only when the posX variable contains a value between 1 and dim(vars).

Re: Assign a value from a table to a macro

Anita_n — Tue, 19 Sep 2023 17:25:56 GMT

if not missing(pos1)

this solved the problem. Thanks to you all for the help

Re: Assign a value from a table to a macro

Tom — Tue, 19 Sep 2023 17:55:15 GMT

@Anita_n wrote:
if pos1 in (1:3) then color = array_name[pos1] ;
this solved the problem. Thanks to you all for the help

For checking that the index is valid it might be better to use the IN operator. You can use the special syntax for indication a range of integers. That will handle the missing values but also the too large or too small or non-integer values also.

So if the size (dimension) of the array is 3 then you could use:

array list var1-var3;
if pos1 in (1:3) then color=list[pos1];