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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.