topic Re: Macro with Two &'s in SAS Programming

Macro with Two &'s

Thu, 27 Apr 2017 18:52:16 GMT

I have many datasets with a sequential naming rule (e.g., Post_1,...,Post_3; but in real-life there are hundreds of datasets).

I want to pull out a certain value from all of the datasets and put it in a %PUT with a sequential naming rule for later use in my code. I have created a version below of what I want to do: I create 3 toy datasets just for a working example, I then try to pull out the X value in the same record for the row with the lowest Y value. In the first example I do this for just one dataset to reveal what I desire. Though, in actual application, I want to do this for a whole bunch of datasets (instead of just Post_1, I would have Post_1-Post_100; So then I would have XMinY_1 through XMinY_100 that I can use later on. The second code section is my attempt to do this, but it does not quite work.

So the overview, I have many datasets that I want to pull a value from each dataset (unique to that set) to put in many sequentially numbered %PUT 's?

Please let me know if you need any more information, I guessing it doesn't like the two & in the put line.

data Post_1(keep= x y i);
	call streaminit(123);
		do i = 1 to 50;
			X = rand("Normal",0, 1);
			Y = rand("Normal",2, 1);
			output;
		end;
run;
data Post_2(keep= x y i);
	call streaminit(1234);
		do i = 1 to 50;
			X = rand("Normal",0, 1.1);
			Y = rand("Normal",2.1, 1);
			output;
		end;
run;
data Post_3(keep= x y i);
	call streaminit(12345);
		do i = 1 to 50;
			X = rand("Normal",0, 2.0);
			Y = rand("Normal",2.2, 1);
			output;
		end;
run;
Proc SQL;
select X into :XMinY_1
from Post_1
having Y=min(Y);
quit;
%put &XMinY_1;

Above functions for a single data set, below attempt at doing this over and over for many datasets.


%MACRO Sets(Start, Stop);
%DO ID_set = &START %TO &STOP;
Proc SQL;
select X into :XMin2Y_&id_set
from Post_&id_set
having Y=min(Y);
quit;
%put &XMin2Y_&id_set;
%END;
%MEND Sets;
%Sets(1,3)

Re: Macro with Two &'s

PaigeMiller — Thu, 27 Apr 2017 18:58:36 GMT

I haven't tested this, but I think the solution is very simple (and you can test it faster than I can)

%put &&XMin2Y_&id_set;

Re: Macro with Two &'s

thomp7050 — Thu, 27 Apr 2017 19:04:01 GMT

If Paige's doesn't work, here is my procedure for multiple executions:

data Post_1(keep= x y i);
	call streaminit(123);
		do i = 1 to 50;
			X = rand("Normal",0, 1);
			Y = rand("Normal",2, 1);
			output;
		end;
run;
data Post_2(keep= x y i);
	call streaminit(1234);
		do i = 1 to 50;
			X = rand("Normal",0, 1.1);
			Y = rand("Normal",2.1, 1);
			output;
		end;
run;
data Post_3(keep= x y i);
	call streaminit(12345);
		do i = 1 to 50;
			X = rand("Normal",0, 2.0);
			Y = rand("Normal",2.2, 1);
			output;
		end;
run;

/*CREATE A DATASET OF NUMBERS 1-3.  CAN CHANGE TO 100 OR WHATEV.*/

proc sql outobs=3;
create table ids as
select monotonic() as id from sashelp.bmimen;
quit;

%macro findmin(id);
Proc SQL;
select X into :XMinY_%str(&id)
from Post_%str(&id)
having Y=min(Y);
quit;
%mend;

data ids;
set ids;
call execute("%findmin("||strip(id)||")");
run;

Re: Macro with Two &'s

Thu, 27 Apr 2017 20:55:22 GMT

As it always goes, I am out the door, but Paige's suggestion seems to work. I will verify tomorrow and accept it as a solution. I will also review thomp7050's suggestion as an alternative.

Thank you.

Re: Macro with Two &'s

Tom — Thu, 27 Apr 2017 21:35:14 GMT

Why not just let PROC SUMMARY do the calculations for all of the datasets at once and avoid the macro variables? Note that putting numbers into macro varaibles can cause the values to be rounded.

data all ;
  length dsname dsn $41 ;
  set post_1-post_3 indsname=dsname ;
  dsn=dsname ;
run;
proc summary data=all nway ;
  class dsn ;
  var y ;
  output out=want( drop=_: ) idgroup(min(Y) out(x)=minxy) ;
run;
proc print data=want; 
run;

Re: Macro with Two &'s

ArtC — Sat, 29 Apr 2017 05:47:06 GMT

@Tom Nice solution. IDGROUP gets so little credit for the variety of things that it can do.

One simplification might be to the SET statement:

set post_: indsname=dsname ;

Re: Macro with Two &'s

Tue, 16 May 2017 14:48:46 GMT

Well, I got burried at work and I am now just getting be to this question. Paige's reply seems to work when running the following code. The RESULTS window it kicks out the right values and those respective values also appear to show up in the LOG window, implying the "PUT" worked. Though, I seem to be unable to use the values that are supposed to be in the PUT, see last piece of code. Any assistance would be appreciated, I am willing to try the other suggestions, but for the entirety of my code, which is very very long, the PUT value must work, since I insert those values repeatedly throughout the code. Thanks in advance for any suggestions.

data Post_1(keep= x y i);
	call streaminit(123);
		do i = 1 to 50;
			X = rand("Normal",0, 1);
			Y = rand("Normal",2, 1);
			output;
		end;
run;
data Post_2(keep= x y i);
	call streaminit(1234);
		do i = 1 to 50;
			X = rand("Normal",0, 1.1);
			Y = rand("Normal",2.1, 1);
			output;
		end;
run;
data Post_3(keep= x y i);
	call streaminit(12345);
		do i = 1 to 50;
			X = rand("Normal",0, 2.0);
			Y = rand("Normal",2.2, 1);
			output;
		end;
run;
;

%MACRO Sets(Start, Stop);
%DO ID_set = &START %TO &STOP;
Proc SQL;
select X into :XMin2Y_&id_set
from Post_&id_set
having Y=min(Y);
quit;
%put &&XMin2Y_&id_set;
%END;
%MEND Sets;
%Sets(1,3)

proc univariate data=post_3;
	where x lt &XMin2Y_1;
	var x;
	histogram x;
	title '&XMin2Y_1 cutoff';
run;

Re: Macro with Two &'s

Tom — Tue, 16 May 2017 14:52:22 GMT

If you create a new macro variable inside of a macro it will default to being local to the macro.

Either define the macro variable before calling the macro

%let XMin2Y_1=;
%let XMin2Y_2=;
%let Xmin2Y_3=;
%Sets(1,3)

or add a %GLOBAL statement to the macro so that it will create a GLOBAL macro variable.

%MACRO Sets(Start, Stop);
proc sql noprint;
%DO ID_set = &START %TO &STOP;
%global XMin2Y_&id_set;
select X
  into :XMin2Y_&id_set
  from Post_&id_set
  having Y=min(Y)
;
%put &&XMin2Y_&id_set;
%END;
quit;
%MEND Sets;

Re: Macro with Two &'s

Tue, 16 May 2017 15:12:48 GMT

Thank you. Can you help show me where the appropriate "%GLOBAL" syntax needs to be added. I am currently searching the web for a good example.

Re: Macro with Two &'s

Tom — Tue, 16 May 2017 15:23:39 GMT

See the code I posted. THe %GLOBAL statement needs to be inside the %DO loop.

Re: Macro with Two &'s

Tue, 16 May 2017 17:03:45 GMT

Thank you for your time and assistance. The code seems to work, but I am unable to figure out how to then use those data saved in the PUT. I have reposted the previous code and added the component that I am having difficulties with. Any direction would be appreciated.

data Post_1(keep= x y i);
	call streaminit(123);
		do i = 1 to 50;
			X = rand("Normal",0, 1);
			Y = rand("Normal",2, 1);
			output;
		end;
run;
data Post_2(keep= x y i);
	call streaminit(1234);
		do i = 1 to 50;
			X = rand("Normal",0, 1.1);
			Y = rand("Normal",2.1, 1);
			output;
		end;
run;
data Post_3(keep= x y i);
	call streaminit(12345);
		do i = 1 to 50;
			X = rand("Normal",0, 2.0);
			Y = rand("Normal",2.2, 1);
			output;
		end;
run;
data Post_4(keep= x y i);
	call streaminit(12345);
		do i = 1 to 50;
			X = rand("Normal",0, 2.0);
			Y = rand("Normal",2.2, 1);
			output;
		end;
run;

%MACRO Sets(Start, Stop);
proc sql ;
%DO ID_set = &START %TO &STOP;
%global XMin2Y_&id_set;
select X
  into :XMin2Y_&id_set
  from Post_&id_set
  having Y=min(Y)
;
%put &&XMin2Y_&id_set;
%END;
quit;
%MEND Sets;
%Sets(1,4)



/*portion that is getting tripped up*/
/*I named the below macro set instead of sets*/

%MACRO Set(Start, Stop);
%DO ID_set = &START %TO &STOP;
proc univariate data=post_&ID_set;
	where x lt &XMin2Y_&ID_set;
	var x;
	histogram x;
	title '&XMin2Y_1';
run;
%END;
%MEND Set;
%Set(1,3)

Re: Macro with Two &'s

Tue, 16 May 2017 18:01:33 GMT

I think I got it with this:

%MACRO Setss(Start, Stop);
%DO ID_set = &START %TO &STOP;
proc univariate data=post_&ID_set;
	where x lt &&XMin2Y_&ID_set;
	var x;
	histogram x;
	title '&XMin2Y_1';
run;
%END;
%MEND Setss;
%Setss(1,3)