Hi! I would like to store the top 3 values of every id & create a column out of it. And let's say there are only two values present, the columns should be blank
data haveOne;
input id value $;
datalines;
1 ABC
1 EFG
1 XYZ
1 UVW
2 GHJ
2 XYZ
3 ABC;
run;
ID | Value |
1 | ABC |
1 | EFG |
1 | XYZ |
1 | UVW |
2 | GHJ |
2 | XYZ |
3 | ABC |
WantOne
ID | Value_1 | Value_2 | Value_3 |
1 | ABC | EFG | XYZ |
2 | GHJ | XYZ | . |
3 | ABC | . | . |
Any kind of help would be appreciated, thanks!
@shubham_d wrote:
For a particular id, I want to store only the first 3 entries
Got it.
proc transpose data=haveone out=want(keep=id value_1-value_3) prefix=value_;
by id;
var value;
run;
What do you mean by “top 3 values”, if the values are character strings?
So when I ask "what do you mean by top 3 values", you cannot answer with you want the "top 3 values". That doesn't explain anything.
@shubham_d wrote:
For a particular id, I want to store only the first 3 entries
Got it.
proc transpose data=haveone out=want(keep=id value_1-value_3) prefix=value_;
by id;
var value;
run;
ID = 3 is not in your data?
You can do it using an ARRAY.
First let's create your dataset. Remember to place the semicolon that ends the data step with in-line data on its own line. Otherwise anything on the line with semicolon is ignored. There is no need to add an extra RUN; statement after the end of the data step.
data have;
input id value $;
datalines;
1 ABC
1 EFG
1 XYZ
1 UVW
2 GHJ
2 XYZ
3 ABC
;
So to know where in the array to place a value you need to count how many observation you have found. Here is one way using a DO loop around the SET statement. This helps as then there is no need to RETAIN the values since all of them for a given ID group are done in one iteration of the data step.
data want;
do rows=1 by 1 until(last.id);
set have;
by id;
array out $8 value_1-value_3;
if rows in (1:3) then out[rows]=value;
end;
drop value;
run;
Result:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.