topic Re: Short question about filling down column with a data value in New SAS User

Short question about filling down column with a data value

hellorc — Fri, 14 Oct 2022 19:18:08 GMT

Closed

Re: Short question about filling down column with a data value

svh — Fri, 14 Oct 2022 18:40:02 GMT

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;

Re: Short question about filling down column with a data value

hellorc — Fri, 14 Oct 2022 19:05:31 GMT

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!

Re: Short question about filling down column with a data value

ballardw — Fri, 14 Oct 2022 19:13:28 GMT

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!