BookmarkSubscribeRSS Feed
Nieves
Quartz | Level 8

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

6 REPLIES 6
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

AS @Kurt_Bremser 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.

Nieves
Quartz | Level 8

Thanks for the advice from @Kurt_Bremser 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!

Kurt_Bremser
Super User

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?

Nieves
Quartz | Level 8

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

Kurt_Bremser
Super User

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(!)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 801 views
  • 2 likes
  • 3 in conversation