topic Proc means with a macro variable in SAS Programming

Proc means with a macro variable

Onizuka — Fri, 03 May 2019 09:43:12 GMT

Hello everyone,

I have to do lot of proc means changing variables one by one on a macro.

Is it possible to say something like :

%do variable = "firstVar of the macro variable" %to "lastVar of the macro variable"

This is an extract of a code :

%macro data_niv ;

	%DO i = 1 %TO 3 ;

		proc means data = data_niv&i noprint ;
			class cniv1_cniv&i ;
			var cim_q1_i1_ ;
			where cim_q1_i1_ ne . ;
			OUTPUT OUT=resALL N=resall;
		run ;
			
		proc means data = data_niv&i noprint;
			class cniv1_cniv&i ;
			var cim_q1_i1_ ;
			where cim_q1_i1_ in(1);
			OUTPUT OUT=resTS N=resTS;
		run ;	

%END ;

%mend ;

As you can see, i put mi first variable "cim_q1_i1" and it's work. But I have lot a variables and i have these variables names on a macro variable called "var_name" but i don't know how execute one by one each variable of the list..

Any ideas ?

Onizuka

Re: Proc means with a macro variable

gamotte — Fri, 03 May 2019 10:01:28 GMT

Hello,

Use a counter and the %scan functions to retrive the successive elements of your list :

%macro loop(vars);

    %local i;

    %do i=1 %to %sysfunc(countw(&vars.));
        %let var=%scan(&vars.,&i.);
        %put &=var.;
    %end;

%mend loop;

%loop(foo bar baz);

Re: Proc means with a macro variable

Onizuka — Fri, 03 May 2019 10:20:59 GMT

Thank you very much @gamotte 🙂

Re: Proc means with a macro variable

data_null__ — Fri, 03 May 2019 12:49:11 GMT

Show an example of your data. I doubt you need all that looping and can reduce the number of call to PROC MEANS.

Re: Proc means with a macro variable

Onizuka — Fri, 03 May 2019 13:30:47 GMT

Hello 🙂

Thank you for reply @data_null__

It's a little hard for me to show you an example of data because there is toooo much data ^^

For information, know that in the real code, there are 5 proc means (and not 2)

I know that I can optimize the code but i'm trying first to make it work haha

%macro data_niv ;

	%DO i = 1 %TO 3 ;


		Proc sort data = mca.ajout_cle_niveau out = test ; by cniv1_cniv&i ; run ;

		Data data_niv&i ;
			set test ;
				where cniv1_cniv&i ne "" ;
				where lastPeriode&i ne . ;
			by cniv1_cniv&i ;
			if date >= dt_deb_periode_niv&i ;
		run ;


		%do j = 1 %to %sysfunc(countw(&var_tsi.));
		%let var = %scan(&var_tsi.,&j.);
		
				proc means data = data_niv&i noprint ;
					class cniv1_cniv&i ;
					var &var. ;
					where &var. ne . ;
					OUTPUT OUT=resALL N=&var._resall;
				run ;
					
				proc means data = data_niv&i noprint;
					class cniv1_cniv&i ;
					var &var. ;
					where &var. in(1);
					OUTPUT OUT=resTS N=&var._resTS;
				run ;		

				proc means data = data_niv&i noprint;
					class cniv1_cniv&i ;
					var &var. ;
					where &var. in(2);
					OUTPUT OUT=resS N=&var._resS;
				run ;

				proc means data = data_niv&i noprint;
					class cniv1_cniv&i ;
					var &var. ;
					where &var. in(3);
					OUTPUT OUT=resI N=&var._resI ;
				run ;			

				proc means data = data_niv&i noprint;
					class cniv1_cniv&i ;
					var &var. ;
					where &var. in(4);
					OUTPUT OUT=resTI N=&var._resTI;


				data synthese_&var._niv&i. ;
					merge resALL resTS resS resI resTI;
					by cniv1_cniv&i ;

						if &var._resALL ne . then do;

							if &var._resTS = . then &var._resTS = 0 ;
							if &var._resS  = . then &var._resS  = 0 ;
							if &var._resI  = . then &var._resI  = 0 ;
							if &var._resTI = . then &var._resTI = 0 ;

							&var._resTSI = ( &var._resTS - ( &var._resI + &var._resTI )) / &var._resall ;
							&var._resTS  = &var._resTS / &var._resall ;
							&var._resS   = &var._resS  / &var._resall ;
							&var._resI   = &var._resI  / &var._resall ;
							&var._resTI  = &var._resTI / &var._resall ;

						end;

					if &var._resALL = . then delete ;
					if cniv1_cniv&i = "" then delete ;
					drop _type_ _freq_ ;
				run ;


				Data synthese_&var._niv&i. (rename=(cniv1_cniv&i = code_niv));
					set synthese_&var._niv&i. ;

						format question $20. ;
						question = "&var."; ;
						zone = "cniv&i" ;

				run ;
		/* I'm working on this step below */		
				%if &j = 1 %then %do ;
					Data test1 ;
					set synthese_&var._niv&i.;
					run ;
				%end ;
				%else %do ;
					Proc sql ;
					insert into test1 
					select * from synthese_&var._niv&i. 
					quit ;
				%end ;

		%end ;

	%END ;

%mend data_niv ;

Re: Proc means with a macro variable

data_null__ — Fri, 03 May 2019 14:10:50 GMT

I do not suggest that you would show all of your data. If you have a lot of data reducing the number of times you read it becomes more important. I'm sure you know best.

Re: Proc means with a macro variable

Onizuka — Fri, 03 May 2019 14:18:53 GMT

@data_null__ wrote:
I do not suggest that you would show all of your data. If you have a lot of data reducing the number of times you read it becomes more important. I'm sure you know best.

I will try !! When my code works, I will come back here for some advice for optimize the code 🙂

thank you 🙂