DATA Step, Macro, Functions and more

how to find the max value

Reply
Occasional Contributor
Posts: 5

how to find the max value

[ Edited ]

hey All,

 

PROC IMPORT OUT= WORK.auto21
            DATAFILE= "C:\Documents and Settings\sasadm\Desktop\XXX.xls"
            DBMS=EXCEL REPLACE;
     SHEET="company_master_data_mar_2015_De";
     GETNAMES=YES;
     MIXED=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

 

proc sort data=auto21 (where=(COMPANY_STATUS='ACTIVE' AND COMPANY_CLASS='Public')) out= ACT12;
by COMPANY_STATUS descending _PAIDUP_CAPITAL_;
run;

 

Till now i code is running fine..!! Now i want the highest paidup cap (Value) in data set...!! Please advise ..? 

how can i go furthre ..???

 

Super Contributor
Posts: 490

Re: how to find the max value

This can be done in different ways. For example you can use MEANS procedure  to get the max min values for each variable.

 

Give example of your dataset and how you want it to be ... In order to get the best answer for your case.

Trusted Advisor
Posts: 1,204

Re: how to find the max value

Seems like you are trying to get maximum value of paidup_capital within each company_status. 

 

proc sql;

select COMPANY_STATUS,max(_PAIDUP_CAPITAL_) as highest_paidup from auto21

where COMPANY_STATUS='ACTIVE' AND COMPANY_CLASS='Public'

group by COMPANY_STATUS;

quit;

 

If you want to get maximum value overall then try this

 

proc sql;

select max(_PAIDUP_CAPITAL_) as highest_paidup from auto21

where COMPANY_STATUS='ACTIVE' AND COMPANY_CLASS='Public';

quit;

 

Super User
Posts: 5,082

Re: how to find the max value

Given that you are comfortable with sorting your data as a starting point, this would be one way to continue:

 

data highest;

set act12;

by COMPANY_STATUS descending _PAIDUP_CAPITAL_;

if last.COMPANY_STATUS;

max_paidup_capital = _PAIDUP_CAPITAL_;

run;

 

This would give you one observation per COMPANY_STATUS and discard the remaining observations.

 

If your intent is slightly different ... to add that max value to EVERY observation ... you could drop variables that aren't needed and then merge with ACT12.  However, note that a more sophisticated program originally can accomplish that in a single step.  After sorting but REMOVING the word DESCENDING from the original BY statement:

 

data want;

do until (last,.COMPANY_STATUS);

   set act12;

   by company_status _paidup_capital_;

run;

max_paidup_capital = _paidup_capital_;

do until (last.COMPANY_STATUS);

   set act12;

   by company_status;

   output;

end;

run;

 

Note that the SQL approaches are equally valid.  I just imagined that you would be more comfortable adding to your original approach.

 

Good luck.

Super User
Posts: 10,500

Re: how to find the max value

With proc means or summary the data need not be sorted first if using a CLASS statement to group your data.

 

proc summary data = data=auto21 nway; /* NWAY says to only output the full combinations when using class variables, don't use if you want to examine all of the combinations of class variables*/

  /* where is also a valid statement in some procs*/

   where COMPANY_STATUS='ACTIVE' AND COMPANY_CLASS='Public';

   /* grouping variables*/

   class COMPANY_STATUS;

   /* analysis variables*/

   var _PAIDUP_CAPITAL_;

   output out= act12(drop=_type_ _freq_) max=;

run;

 

_type_ is a special variable that describes which combination of class variables the output record belongs to, _freq_ is how many records were examined to get the statistics.

Ask a Question
Discussion stats
  • 4 replies
  • 234 views
  • 1 like
  • 5 in conversation