Hi need assistance with a table that was imported into SAS the table has 3 columns
unit 1. $100 05/01/12
unit 2. $59. 05/12/12
unit 2. 33. 05/13/12
unit 3. $ 60. 05 /12/12
unit 3. 55. 05/20/12
unit 1 $25 05/05/12
what I need is to Id only the max $ value by date
THis is what I'm looking for
unit 1 $100 05/01/12
unit 2 $59 05/12/12
unit 3 $60 05/12/12
what is coming up is max value for each day which brings everything in...thank you for you assistance
Simply add the date to the list of fields that you want :
proc sql;
create table maxValue as
select
unit,
date,
intnx("MONTH",date,0) as month format = yymmd7.,
value
from have
group by
unit,
calculated month
having value=max(value);
select * from maxValue;
quit;
PG
Try this :
data have;
input @5 unit value :comma5. date : mmddyy8.;
format date date9.;
datalines;
unit 1. $100 05/01/12
unit 2. $59. 05/12/12
unit 2. 33. 05/13/12
unit 3. $60. 05/12/12
unit 3. 55. 05/20/12
unit 1 $25 05/05/12
;
proc sql;
create table want as
select
unit,
intnx("MONTH",date,0) as month format = yymmd7.,
value
from have
group by
unit,
calculated month
having value=max(value);
select * from want;
quit;
PG
HI PGStats,
thanks you for responding to my question. It's real close you script did bring out the max value and the unit # the only thing that is missing is the date of when the max value occurred . what appear is the month 05/2012. I need the date brought over with it when it hit max value...thanks again for assistance
Simply add the date to the list of fields that you want :
proc sql;
create table maxValue as
select
unit,
date,
intnx("MONTH",date,0) as month format = yymmd7.,
value
from have
group by
unit,
calculated month
having value=max(value);
select * from maxValue;
quit;
PG
HI PGStats,
thank you for help. I must be doing something wrong because when I run the script it doesn't give me the highest usage $ per day of the month select. It gives me lowest Amt. what do you think is causing that that? Thanks again for all your help
Are you sure VALUE is a numeric variable? If it is character then it will take the max when sorting alphabetically. So '9' would be "greater" then '1000'.
Hi,
What about this ???
proc sort data = have;
by unit value;
run;
data want;
set have;
by unit value
if last.unit and last.value then output;
run;
Hope it meets your requirement...
-Urvish
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.