BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
noobs
Calcite | Level 5

Data structure:

PriceTheMonthTheYear
1012013
1112013
1512012
1222013
1322012

I am struggling to compute max value of Price variable for each combination of TheMonth and TheYear

Final Result Expected:

PriceTheMonthTheYear
1112013
1512012
1222013
1322012

I believe I cannot use MAX() function directly as it is not possible to define the array.

Will DO statement resolve it? in conjunction with BY statement after data is sorted by TheYear and TheMonth; sort of hazy on how to hash out the logic

Appreciate the feedback.

Thankfully,

Dhanashree Gadkari

1 ACCEPTED SOLUTION

Accepted Solutions
esjackso
Quartz | Level 8

Another alternative would be to use group by in SQL:

Proc sql;

     create table max as

     select max(price) as Mprice, themonth, theyear

     from yourdata

     group by themonth, theyear

;

quit;

If you dont need a dataset you can leave the table statement off and the report will output.

EJ

View solution in original post

6 REPLIES 6
Rick_SAS
SAS Super FREQ

The simplest way is to bypass the DATA step and use PROC MEANS:

proc means data=... max;

class TheMonth TheYear;

var Price;

run;

Ksharp
Super User

there is need a option:

proc means nway ;

or use BY instead of CLASS.

Ksharp

Rick_SAS
SAS Super FREQ

The NWAY option only affects an output data set.  For printed output, it doesn't matter:

proc means data=sashelp.cars max  /* NWAY */;

class origin type;

var mpg_city;

run;

esjackso
Quartz | Level 8

Another alternative would be to use group by in SQL:

Proc sql;

     create table max as

     select max(price) as Mprice, themonth, theyear

     from yourdata

     group by themonth, theyear

;

quit;

If you dont need a dataset you can leave the table statement off and the report will output.

EJ

noobs
Calcite | Level 5

That was it. You resolved it esjackso1 thanks a bunch.

noobs
Calcite | Level 5

Although the output of your suggestion is what I am exactly looking for, I need to restore data structure of output statistics as shown in Final data structure above for further plotting bubble charts  .

So do you suggest using OUTPUT statement in proc MEANS, to capture Price, TheYear, TheMonth?

Not sure how to delete unwanted columns like _TYPE_ _FREQ_ and keep only MAX stat in proc step

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
  • 812 views
  • 0 likes
  • 4 in conversation