BookmarkSubscribeRSS Feed
H
Pyrite | Level 9 H
Pyrite | Level 9

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)

 

11 REPLIES 11
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
H
Pyrite | Level 9 H
Pyrite | Level 9

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;

 

 

Tom
Super User Tom
Super User

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;

 

H
Pyrite | Level 9 H
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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

H
Pyrite | Level 9 H
Pyrite | Level 9

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)

 

H
Pyrite | Level 9 H
Pyrite | Level 9

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)
thomp7050
Pyrite | Level 9

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;

 

 

H
Pyrite | Level 9 H
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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;

Capture.PNG

ArtC
Rhodochrosite | Level 12

@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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1014 views
  • 3 likes
  • 5 in conversation