topic Re: how to select maximum values for all variables with the same prefix in SAS Programming

how to select maximum values for all variables with the same prefix

catkat96 — Wed, 12 May 2021 09:54:19 GMT

I have something like this:

proc sql;
create table want as 
select id
, date
, v3
, v4
, max(d:)

from have
group by id, date;
quit;

Where there are d1, d2, d3, d4, d5, d6, etc.

The system is throwing an error in the max part of the code, I assume because saying max(d:) is not right.

Is there a way to do something like this?

Alternatively I tried

proc summary data=have nway;
class id date;
var d: v3 v4;
output out=want max=;
run;

But it's not workking because v3 and v4 are not numeric. If I don't include v3 and v4 it runs, but I need to include those.

Edit: to give a bit more context, I want to do this to "merge" rows with the same ID, to end with one row per ID per date.

Edit 2: I want to end with all d columns as columns.

Re: how to select maximum values for all variables with the same prefix

Ksharp — Wed, 12 May 2021 11:53:45 GMT

Add one more Data Step ?

proc summary data=have nway;
class id date;
var d: ;
output out=want max=;
run;
data final_want;
merge have(keep=id date v3 v4) want;
by id date;
run;

Re: how to select maximum values for all variables with the same prefix

catkat96 — Wed, 12 May 2021 14:21:54 GMT

How can I do it so that only the last row of each id from have is merged with want?
I added the variable time as well, and did the merge by id date and time and it's merging rows with different times for some reason

Re: how to select maximum values for all variables with the same prefix

ballardw — Wed, 12 May 2021 15:35:01 GMT

@catkat96 wrote:
How can I do it so that only the last row of each id from have is merged with want?
I added the variable time as well, and did the merge by id date and time and it's merging rows with different times for some reason

1) Show your code

2) Show some data for the two sets merged.

3) show the desired result.

If one data set in a MERGE has values of the BY variables that do not appear in the other then you get both. If you require the output to only include matches from one of the data sets you use the IN data set option to create contribution indicators such as;

data want; 
   merge one (in=inone)
         two (in=intwo)
   ;
   by somevar;
   if inone; /* select records that have matches from data set one*/
;

Th IN option creates a 1/0 (true/false) temporary variable that indicates if the current record has contributions from that data set. Temporary means the variable is not written to the output data set.

If inone=1 and intwo= 1 then both data sets contribute

if inone=1 and intwo=0 then the current record only has items from data set one

if inone=0 and intwo=1 then the current record only has items from data set two

So this might be a solution to "time" values in one set but not the other that you are seeing. Might.