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 ..???
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.
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.