Short question about filling down column with a data value

[hellorc]

Closed

[svh]

This kind of transformation is ideally suited for PROC SQL with a GROUP BY statement, which allows you to derive group-level variables that can be added to your data set.

proc sql;
   create table have2 as
   select a.id, min(a.a) as a, a.b, a.c
   from have a 
   group by a.id;
   quit;

[hellorc]

Hello svh, thank you so much for your quick answer. It was so helpful!! I have an extra question, in future I may have many more variables in 'have', so in your code for the line I will have so many extra 'a.something':

select a.id, min(a.a) as a, a.b, a.c, a.d, a.e, a.f, ...so many more a.variable

Is there any way or syntax to suppress ALL available variables, something like a.all ?? Thanks again!

[ballardw · Accepted solution]

A data step based solution, different sort as I don't think your sort does what you need.

proc sort data=have;
   by id a;
run;

data want;
   set have;
   by id;
   retain tempa;
   if first.id then tempa=a;
   a=tempa;
   drop tempa;
run;

By group processing for ID, which is your rule, First.Id to set a value of a Retained variable that keeps the first value of A and overwrites all the A, then dropped.

Caution: You did not describe any rule for what to do if the "smallest" value of A is missing. Missing is smaller than anything. If you don't want the other values replaced with missing then you need to provide some more rules.

---

@hellorc wrote:

Hello SAS community, I have a dataset that looks like

data have;
input id a b c @@;
datalines;
1 8 5 2
1 1 3 7
1 9 8 4
1 2 6 4
2 2 5 9
2 5 3 7
2 5 7 9
3 6 4 3
3 2 5 7
;
run;

For each id, I would like to sort by a variable, say 'a', from smallest to largest with the code:

proc sort data=have;
by a;
run;

Then, for each id, I would like to fill every 'a' variable entries with ONLY the smallest data value, so what I want is:

data want;
input id a b c @@;
datalines;
1 1 3 7
1 1 6 4
1 1 5 2
1 1 8 4
2 2 5 9
2 2 3 7
2 2 7 9
3 2 5 7
3 2 4 3
;
run;

Might someone please help me with such manipulation technique? Thank you ahead!

---