find the maximum value of variables y and z among each group of id x

Reply
Contributor
Posts: 20

find the maximum value of variables y and z among each group of id x

In a panel data defined by id x and time t, how to find the maximum value of variable y (max y) and maximum value of variable z (max z) within each group of id x?

 

Then Compare the maximum value of y and maximum value of z, create a varialbe that equals the maximum (max y,max z) and identify the time when the maximum value of y and z appears respectively? 

 

I'm using SAS 9.4

Super User
Posts: 7,431

Re: find the maximum value of variables y and z among each group of id x

When you posted your question, you saw this:

 

Stop right there! Before pressing POST, tick off this checklist. Does your post …

✔ Have a descriptive subject line, i.e., How do I ‘XYZ’? ✔ Use simple language and provide context? Definitely mention what version you’re on. ✔ Include code and example data? Consider using the SAS Syntax feature.

 

 

 

 

 

 

right beneath. Please note the third section. Give us some data to play around with. Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset to a datastep for posting in a code window (6th or 7th icon above the posting window).

Also include any code you already have.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,720

Re: find the maximum value of variables y and z among each group of id x

AS @KurtBremser has said, to get a good answer, provide good information.  Test data in the form of a datastep and what the output should look like.

As for the code, you a on the way there already:

y (max y)

z (max z) 

group of id x

 

So just wrap that into some SQL:

select max(Y) as Y,
       max(Z) as Z,
max(CALCULATED Y,CALCULATED Z) as FINAL from HAVE group by ID;

For your second point, you would merge the output from the above back to your HAVE dataset and compare final to the value in the original data.  However, what happens if there is more than one which matches?  This is where you need to provide examples.

Contributor
Posts: 20

Re: find the maximum value of variables y and z among each group of id x

Thanks for the advice from @KurtBremser and @RW9

 

I have uploaded a sample data. I have figured out the maximum value of var1 and var2 withint each id group. The next question is that if the maxvar1 = 100 for group i (i=1,2,3), I need to find and store the month_max t  when the maxvar2 occurs in the benchmark group (group 4 in the sample data). After obtaining month_max t, I need to call this value later in the program and divide var1 by maxvar1 for group i in month month_max. 

 

My code is as follows: it seems that my second part of the code has some problem. 

 

proc means data=import_data noprint;
var val1 val2;
by id;
output out = edit_data max=val1_max val2_max;
run;

 

 %MACRO month_max;

data want;
set edit_data ;
by id month;
if val2_max = 100 and id= 4; then month = &month_max;
run;

 

 

Thanks!

Super User
Posts: 7,431

Re: find the maximum value of variables y and z among each group of id x

Read my post again. Excel spreadsheets do not convey the real structure of SAS datasets, and are blocked at many corporate sites for security reasons. Therefore they are useless as example data (only exception: when facing a problem with importing from Excel).

Post your example data (work.import_data) in a data step, using the macro mentioned.

 

Your code is incomplete. There is no %mend statement, so at least that part of the macro is missing. And where did you set the macro variable month_max?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: find the maximum value of variables y and z among each group of id x

My SAS data is attached as follows. I could not upload the data directly. I am wondering whether macro is needed to store the month where the maximum value occured. Would you have any advice on how to store a value and call later?

 

The detailed question is as follows:

if the var1_max = 100 for id i (i=1,2,3), I need to find and store the &month_max of month t  when the var2_max occurs in the benchmark group (group 4 in the sample data). After obtaining month_max t, I need to call this value later in the program and divide var1 by var1_max for id i in month month_max. 

 

Thanks

Capture.JPG

Super User
Posts: 7,431

Re: find the maximum value of variables y and z among each group of id x

Read my posts again.

 

Post your example data in a data step for easy recreation as described in the link I gave you. It's not rocket science, mind.

 

I won't waste my time typing data off puctures(!)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 6 replies
  • 130 views
  • 2 likes
  • 3 in conversation