我有一个数据集,它有三个变量。
Id Region Amount
1 A 20
1 A 40
1 A 50
2 B 40
2 B 30
2 B 60
3 C 10
3 C 30
4 D 20
4 D 50
4 D 10
我想为每个区域创建宏变量,然后将最小数量分配给这些变量。例如,在上述数据集的情况下,答案应该是 4 个宏变量,其值为:
macro_var val
A 20
B 30
C 10
D 10
任何帮助将不胜感激。
将 id
选择到宏变量中,以便您可以遍历它们并找到每个 id 的最小金额值:
data temp;
input Id $ Region $ Amount $;
datalines;
1 A 20
1 A 40
1 A 50
2 B 40
2 B 30
2 B 60
3 C 10
3 C 30
4 D 20
4 D 50
4 D 10
;
run;
proc sql noprint;
select distinct region into: region_list separated by " " from temp;
quit;
%macro assign_vars;
%do i = 1 %to %sysfunc(countw(®ion_list.));
%let this_region = %scan(®ion_list., &i.);
%global min_of_region_&this_region.;
proc sql noprint;
select min(amount) into: min_of_region_&this_region. from temp (where = (region = "&this_region."));
quit;
%end;
%mend assign_vars;
%assign_vars;
%put &min_of_region_A.;
%put &min_of_region_B.;
%put &min_of_region_C.;
%put &min_of_region_D.;
我会在SQL中使用GROUP BY
语句,然后在数据步骤中使用CALL SYMPUT
来创建宏变量:
proc sql;
create table temp2 as
select Region, min(amount) as minamount
from temp
group by Region;
quit;
data _null_;
set temp2;
call symput(Region,minamount);
run;