Use appropriate user defined proc format based on column value

[polksio]

Hello, 

I'm trying to pull a values from user defined proc formats depending on a column value

I have a dataset

ID    x y  a   

R1   1 3 5  

R2   2 4 6   

and 

the user defined proc formats are

proc format;

value R1xa;

1 = 10

2 = 11

quit;

proc format;

value R2xa;

1 = 12

2 = 13

quit;

proc format;

value R1ya;

3 = 14

4 = 15;

quit;

proc format;

value R2ya;

3 = 16

4 = 17

;

quit;

i need to loop over x and y so currently in doing it in this fashion

%macro test;

data output;

set input;

%LET name_list = x y;

%LOCAL i next_name;
%LET i=1;
%DO %WHILE (%SCAN(&name_list, &i) ne );
%LET next_name = %SCAN(&name_list, &i);

format&next_name. = put(a, R1&next_name.a);

%LET i = %EVAL(&i + 1);
%END;

run;

%test;

%mend;

to output the following

ID    x y  a   formatxa formatya

R1   1 3 5        10            14 

R2   2 4 6         11           15

but i would like to use the appropriate proc format based on the value of ID

the desired output would be 

ID    x y  a   formatxa formatya

R1   1 3 5        10            14 

R2   2 4 6         13           17

Thank you,

[Kurt_Bremser]

No need for macro coding, and it won't work anyway.

Use the PUTN function instead of PUT, and determine the format name depending on the id value.

formatxa = putn(x,cats(id,'xa'));
formatya = putn(y,cats(id,'ya'));

[ballardw]

First, make sure you provide format code that actually runs. Every one you yours has the same error of a ; with value before any of the values are listed. You can have many formats defined in a single call to proc format such as:

proc format;
value R1xa
1 = 10
2 = 11
;
value R2xa
1 = 12
2 = 13
;
value R1ya
3 = 14
4 = 15
; 
value R2ya
3 = 16
4 = 17
;
run;
 

Why do you have the variable A in that example? It does not appear to have anything to do with the question.

I also don't see why you have 4 formats. If you want to use ID value of R1 to identify a format with the values you show, no overlap in values between format R1xa (1 and 2) and R1Ya (3 and 4) a single format would work (and reduce coding),

Similar with R2Xa (1 and 2 ) and R2Ya (3 and 4)

This seems to do what you want.

data have;
input ID $    x y  a  ;
datalines;
R1   1 3 5
R2   2 4 6
;

data want; 
   set have;
   array _v (*) x y ;
   array _c (2) $ 2 formattedx formattedy  ;
   do i=1 to dim(_V);
      fmtname= cats(id,vname(_v[i]),'a.');
      _c[i]= putn(_v[i],fmtname);
   end;
run;

When you are doing similar things to multiple variables then Arrays are often a much better tool than delving into macro coding. The two array statements define which variables are in them and the order matches so when the first element of the _v array  is processed it corresponds to the character value created by the format. Arrays also allow for easy use of some of the variable information functions, like VNAME which will pull the name of the variable being processed from the array definition. I have shown code creating the FMTNAME variable to combine the value of the ID variable, the variable name, determined with the VNAME information function, and then stick that A. on the end. There are other versions of the PUT function, Putn and Putc that allow placing the name of a format into a variable, such as FMTNAME above, and then using that value for the Put function. There are also corresponding INPUTN and INPUTC that allow informat names in a similar fashion. The CATS function places the text without any trailing spaces as seen.

The Do loop uses the function DIM to return how many elements are in the array and controls how many variables are processed and the value of the loop counter is used as the index into the array. I use the [ ] to show where the index for the array is used so you can see the difference in the code. You can use () or {} as well. The [ ] makes it a tad easier to

[polksio]

@Kurt_Bremser  wrote:

No need for macro coding, and it won't work anyway.

Use the PUTN function instead of PUT, and determine the format name depending on the id value.

formatxa = putn(x,cats(id,'xa'));
formatya = putn(y,cats(id,'ya'));

Thank you for your answer Kurt, the need for macros come from the fact that there are dozens of "xs and ys" which create the need for macro loops and then more operations are done on the x and y columns so I was trying to integrate the proc format value extraction within said loops, but if its not feasible I'll carve out this step outside the loops with your suggestion.

@ballardw wrote:

Why do you have the variable A in that example? 

---

Thank you for your answer ballardw, you are right, the user defined proc formats should be 

proc format;
value R1xa
5 = 10
6 = 11
;
value R2xa
5 = 12
6 = 13
;
value R1ya
5 = 14
6 = 15
; 
value R2ya
5 = 16
6 = 17
;
run;

%macro test;

data output;
   set input;

   %LET name_list = x y;

   %LOCAL i next_name;
   %LET i=1;
   %DO %WHILE (%SCAN(&name_list, &i) ne );
   %LET next_name = %SCAN(&name_list, &i);

      format&next_name.a = put(a, R1&next_name.a);

   %LET i = %EVAL(&i + 1);
   %END;

run;

%test;

%mend;

---

[ballardw]

---

@polksio wrote:

Thank you for your answer Kurt, the need for macros come from the fact that there are dozens of "xs and ys" which create the need for macro loops and then more operations are done on the x and y columns so I was trying to integrate the proc format value extraction within said loops, but if its not feasible I'll carve out this step outside the loops with your suggestion.

This indicates that perhaps your "example" is incomplete. If your example does not provide enough information to cover all of the cases then there is no way we can provide working solutions.

Still doubt that macro loops are needed.

[polksio]

---

@ballardw wrote:

---

@polksio wrote:

Thank you for your answer Kurt, the need for macros come from the fact that there are dozens of "xs and ys" which create the need for macro loops and then more operations are done on the x and y columns so I was trying to integrate the proc format value extraction within said loops, but if its not feasible I'll carve out this step outside the loops with your suggestion.

This indicates that perhaps your "example" is incomplete. If your example does not provide enough information to cover all of the cases then there is no way we can provide working solutions.

Still doubt that macro loops are needed.

---

Apologies for any incomplete or erroneous info and appreciate your effort @ballardw, the other operations that would be included in this loop are mainly some multiplication and some addition. My intent was to simplify the request but please find below the full routine that is repeated for a couple of other factors. I'm certain as you said macros are not needed but part of the request was to use them.

%Macro step0;

	DATA want;
		SET have;


		attrib sum_prime sum sum_prime_x sum_x format=10.5;

		sum_prime = 0;
		sum = 0;

		sum_prime_x = 0;
		sum_x = 0;


		%LET name_list =  aaa bbb ccc ddd eee fff ggg hhh
						  iii jjj kkk lll mmm nnn ooo ppp qqq rrr sss ttt;  
		
		%LOCAL i next_name;
		%LET i=1; 
		%DO %WHILE (%SCAN(&name_list, &i) ne );
		%LET next_name = %SCAN(&name_list, &i);
			
			attrib &next_name._step0 &next_name._step0_x format=10.5;
			
			&next_name._step0 = &next_name._raw;
			&next_name._step0_x = &next_name._adjusted_raw;

		%LET i = %EVAL(&i + 1);
		%END;


		%LET name_list =  aaa bbb ccc ddd eee fff ggg hhh ; 

		%LOCAL i next_name;
		%LET i=1; 
		%DO %WHILE (%SCAN(&name_list, &i) ne );
		%LET next_name = %SCAN(&name_list, &i);

			p&next_name.factor_prime = put(factor_prime, R1&next_name.factor.); 
			p&next_name.factor = put(factor, R1&next_name.factor.); 

			&next_name._step0 = &next_name._step0 * p&next_name.factor_prime  / p&next_name.factor ;
			&next_name._step0_x = &next_name._step0_x *      p&next_name.factor_prime  / p&next_name.factor ;

		%LET i = %EVAL(&i + 1);
		%END;



		%LET name_list =  aaa bbb ccc ddd eee fff ggg hhh
						  iii jjj kkk lll mmm nnn ooo ppp qqq rrr sss ttt;

		%LOCAL i next_name;
		%LET i=1; 
		%DO %WHILE (%SCAN(&name_list, &i) ne );
		%LET next_name = %SCAN(&name_list, &i);
			
			sum = sum + &next_name._step0;
			sum_x = sum_x + &next_name._step0_x;

			IF &i. < 9 THEN DO;

			sum_prime = sum_prime + &next_name._step0;
			sum_prime_x = sum_prime_x + &next_name._step0_x;

			END;

		%LET i = %EVAL(&i + 1);
		%END;
	RUN;

%MEND;
%step0;

I hope the first example with the corrected proc formats is sufficient, I've been exploring using resolve() call symput, and many other options to resolve a &format_version. macro variable inside the put functions which would integrate well with the program. Any leads you think are promising I can investigate futher? 

Thank you,

[Tom]

Does your posted code work or not?

Turn on the MPRINT option and look at the generated SAS code a decide if it is doing what you want.

This part of the macro makes no sense.

  IF &i. < 9 THEN DO;
    sum_prime = sum_prime + &next_name._step0;
    sum_prime_x = sum_prime_x + &next_name._step0_x;
  END;

So for the first 8 values out of that list of 20 values you will generate an IF statement that is always true. 

if 1 < 9 then do;
...
if 2 < 9 then do;
...

For the other 12 values one that is always false.  It would make more sense to use macro logic to only generate those two statements for the first 8 values in your name list.

  %IF &i. < 9 %THEN %DO;
sum_prime = sum_prime + &next_name._step0;
sum_prime_x = sum_prime_x + &next_name._step0_x;
  %END;

[Tom]

Can you explain what the larger problem is that this approach is trying to solve?

https://xyproblem.info/

[Tom · Accepted solution]

Are you just asking how to generate a series of assignment statements?

So perhaps something like this macro?

%macro gen_put(name_list);
%local i next_name ;
%do i=1 %to %sysfunc(countw(&name_list,%str( )));
  %let next_name=%scan(&name_list,&i,%str( ));
  format&next_name.a = putn(a,cats(id,"&next_name.a."));
%end;
%mend;

Which you could then use in a data step.

options mprint;
data want;
  set have;
%gen_put(x y)
run;

Log

1804  data want;
1805    set have;
1806  %gen_put(x y)
MPRINT(GEN_PUT):   formatxa = putn(a,cats(id,"xa."));
MPRINT(GEN_PUT):   formatya = putn(a,cats(id,"ya."));
1807  run;

NOTE: There were 2 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 2 observations and 6 variables.

Results

Obs    ID    x    y    a    formatxa    formatya

 1     R1    1    3    5       10          14
 2     R2    2    4    6       13          17

[Kurt_Bremser]

When you need to perform same (or similar) operations on series of variables, look into the use of arrays.