topic Re: how to find the max value in SAS Programming

how to find the max value

SAM_VAS — Fri, 25 Dec 2015 10:00:04 GMT

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 ..???

Re: how to find the max value

mohamed_zaki — Fri, 25 Dec 2015 12:31:10 GMT

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.

Re: how to find the max value

stat_sas — Fri, 25 Dec 2015 17:11:14 GMT

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;

Re: how to find the max value

Astounding — Fri, 25 Dec 2015 17:48:20 GMT

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.

Re: how to find the max value

ballardw — Mon, 28 Dec 2015 16:52:37 GMT

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.