Assign the minimum of one variable for each person to all records for that person.

Reply
Contributor
Posts: 30

Assign the minimum of one variable for each person to all records for that person.

My data set looks like this:

ID_TIMEPERIOD_VAR1

1_2_7

1_4_6

2_1_5

2_3_2

2_4_5

2_8_3

.

.

I want to find the MIN(VAR1) for each ID, and assign that value to the new variable MINVAR1.

The above example should become

ID_TIMEPERIOD_VAR1_MINVAR1

1_2_7_6

1_4_6_6

2_1_5_2

2_3_2_2

2_4_5_2

2_8_3_2

:

:

Is it possible to get that in one data step or with one PROC SQL?

Thanks for any help.

Occasional Contributor
Posts: 12

Re: Assign the minimum of one variable for each person to all records for that person.

proc sql;

     create table want as

          select *,min(var1) as minvar1

          from have

          group by 1

          order by 1,2;

quit;

Super User
Super User
Posts: 7,430

Re: Assign the minimum of one variable for each person to all records for that person.

Hi,

Firstly I wouldn't have the three concatenated variables as you have above, any kind of manipulation or calculation on the individual parts would mean splitting them out.  So the below separates the data, then gets min of the new var1 variable.  You could if you really want concatenate the variables back again, however I don't see value there:

data have;
  attrib ID_TIMEPERIOD_VAR1 format=$200.;
  infile datalines;
  input ID_TIMEPERIOD_VAR1 $;
datalines;
1_2_7
1_4_6
2_1_5
2_3_2
2_4_5
2_8_3
;
quit;

proc sql;
  create table WANT as
  select  *,
          MIN(VAR1) as MIN_VAR1
  from    (

            select  ID_TIMEPERIOD_VAR1,
                    scan(ID_TIMEPERIOD_VAR1,1,"_") as ID,
                    scan(ID_TIMEPERIOD_VAR1,2,"_") as TIMEPERIOD,
                    scan(ID_TIMEPERIOD_VAR1,3,"_") as VAR1
            from    HAVE
          )
  group by ID;
quit;

Super User
Posts: 6,977

Re: Assign the minimum of one variable for each person to all records for that person.

data want;

minvar1 = 999999999;

do until last.id;

  set have;

  by id;

  minvar1 = min(minvar1,var1);

end;

do until last.id;

  set have;

  by id;

  output;

end;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,204

Re: Assign the minimum of one variable for each person to all records for that person.

data one;
set have;
ID = scan(ID_TIMEPERIOD_VAR1,1,"_");
TIMEPERIOD = scan(ID_TIMEPERIOD_VAR1,2,"_");
VAR1=scan(ID_TIMEPERIOD_VAR1,3,"_");
run;

proc sort data=one out=two;
by id var1;
run;

data three;
set two;
by id var1;
retain minvar1;
sp='_';
if first.id then minvar1=var1;
ID_TIMEPERIOD_VAR1_MINVAR1=catt(Id,sp,TIMEPERIOD,sp,VAR1,sp,MINVAR1);
run;

proc sort data=three out=want(keep=ID_TIMEPERIOD_VAR1_MINVAR1);
by id TIMEPERIOD;
run;

Ask a Question
Discussion stats
  • 4 replies
  • 229 views
  • 0 likes
  • 5 in conversation