topic Re: Storing Top Values in a column in SAS Enterprise Guide

Storing First 3 Values in a column

shubham_d — Thu, 02 Mar 2023 13:18:09 GMT

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!

Re: Storing Top Values in a column

PaigeMiller — Thu, 02 Mar 2023 12:51:01 GMT

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

Re: Storing Top Values in a column

PeterClemmensen — Thu, 02 Mar 2023 12:59:46 GMT

ID = 3 is not in your data?

Re: Storing Top Values in a column

shubham_d — Thu, 02 Mar 2023 13:04:57 GMT

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

Re: Storing Top Values in a column

PaigeMiller — Thu, 02 Mar 2023 13:06:00 GMT

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.

Re: Storing Top Values in a column

shubham_d — Thu, 02 Mar 2023 13:07:56 GMT

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

Re: Storing Top Values in a column

PaigeMiller — Thu, 02 Mar 2023 13:17:08 GMT

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

Re: Storing Top Values in a column

shubham_d — Thu, 02 Mar 2023 13:20:20 GMT

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.

Re: Storing Top Values in a column

shubham_d — Thu, 02 Mar 2023 13:23:28 GMT

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

Re: Storing First 3 Values in a column

Tom — Thu, 02 Mar 2023 16:48:47 GMT

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: