Storing First 3 Values in a column

[shubham_d]

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! 

[PaigeMiller]

What do you mean by “top 3 values”, if the values are character strings?

[shubham_d]

Hey! So these are already sorted values & I need to create a column of only top 3 values

[PaigeMiller]

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]

For a particular id, I want to store only the first 3 entries, does that make sense ? And if 3 entries are not present for a particular id, I still want the columns to be created but they will have blank values

[PaigeMiller · Accepted solution]

---

@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;

[shubham_d]

Hey! Thanks, this helped. Appreciate your patience in understanding the question. Am new to SAS & aiming to be more efficient next time

[PeterClemmensen]

ID = 3 is not in your data?

[shubham_d]

Sorry missed out the 3rd id in datalines. Updated it now

ID 3 has only one entry & hence when the columns are created the value_1 will have the value & value_2 & value_3 columns will stay blank. I hope that helps.

[Tom]

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: