Problem using call symputx by variable

[Onizuka]

Hello !

Sorry for my english..

How can we create many macro variables using the BY VARIABLE... ?

I have tried something like this :

data test888;   
set pourcomptage ;  
	by cniv1_cniv3 ; 

	if first.cniv1_cniv3 then do ;
		call symputx("V1",col1); 
	end ;

	if last.cniv1_cniv3 then do ; 
		call symputx('V2',sumcum);    
	end ;
run;

The problem is that when i want to call the both macro variable, juste the last macro variable is stored.

These 2 macro variables permit me to calculate later the difference : %let nrep = %eval(&v1 - &v2) and then, on a SQL QUERY, calculate by hand, a percent by variable (cniv1_cniv3).

When I'm doing this :

%let nrep = %eval(&V1 - &V2) ;

Proc sql ;
create table indi_cm as 
select cniv1_cniv3, variable, (sum(count)/&nrep) as percent_mod
from nb_niv
group by cniv1_cniv3, variable ;
quit ;

I don't know how to say : "for the first cniv1_cniv3 you take the first &nrep associate to this cniv1_cniv3"

"for the second cniv1_cniv3, you take the second &nrep" .....

Do you have any idea ?

I don't put an example of data because I think that you can help me without !

Thank you in advance...

Onizuka

[Kurt_Bremser]

This is exactly what the macro engine is NOT for. Forget that macros exist at all (for the moment), you need to learn handling the Base SAS language first.

data test888;   
set pourcomptage ;  
by cniv1_cniv3 ; 
retain _col1;
if first.cniv1_cniv3 then _col1 = col1;
if last.cniv1_cniv3 then do ; 
  diff = sumcum - _col1; /* or whatever you want to calculate for the group */
  output;
end ;
run;

[Tom · Accepted solution]

In general it is not a good idea to transfer values into macro variables just so you can later translate them back into values.

Keep the data in datasets.  Your code is not consistently referencing the same input dataset, but most likely you want to summarize the values 

data summary;    
   set pourcomptage ;  
   by cniv1_cniv3 ; 
   if first.cniv1_cniv3 then first_value=col1;
   if last.cniv1_cniv3 then do ; 
      last_value=sumcum;
      output;
   end;
   retain first_value;
   keep cniv1-cniv3 first_value last_value;
run;

and then merge/join the summary data back with the other data.

proc sql ;
create table indi_cm as 
  select a.cniv1_cniv3, a.variable
       , (sum(count)/(b.first_value - b.last_value) as percent_mod
  from nb_niv a
  left join summary b 
  on a.cniv1_cniv3 = b.cniv1_cniv3
  group by a.cniv1_cniv3, a.variable 
;
quit ;

[Onizuka]

Hello all,

Thank you a lot for all you responses, I think that the solution of @Tom is working perfectly and so as the solution of @Kurt_Bremser !

I don't know why I didn't think about doing a simple merge... !

I tried to do something similar, I did not think for a single second to the instruction "output"

[Reeza]

>I don't put an example of data because I think that you can help me without !

It's significantly easier to help you with it though.