topic Re: Problem using call symputx by variable in SAS Programming

Problem using call symputx by variable

Onizuka — Wed, 29 May 2019 14:36:36 GMT

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

Re: Problem using call symputx by variable

Kurt_Bremser — Wed, 29 May 2019 14:45:19 GMT

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;

Re: Problem using call symputx by variable

Tom — Wed, 29 May 2019 14:49:15 GMT

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 ;

Re: Problem using call symputx by variable

Reeza — Wed, 29 May 2019 16:29:41 GMT

>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.

Re: Problem using call symputx by variable

Onizuka — Fri, 31 May 2019 07:27:25 GMT

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"