How to create Macro variable for each unique region and assigned minimum amount to respective macro variable

Question

I have a dataset which have three variable.below is the example dataset.

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

I want to create macro variable for each region and then assigned the minimum amount to those variable. For example in case of above dataset answer should be 4 macro variable with their value as:

macro_var   val
A           20
B           30
C           10
D           10

Any help would be highly appreciated.

Answer 1

Select the ids into a macro variable so that you can then loop through them and find the minimum value of amount for each:

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(&region_list.));
    %let this_region = %scan(&region_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.;

Answer 2

I would utilize GROUP BY statement in SQL and then use CALL SYMPUT in a data step to create macrovariables:

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;