Maximum Value for each day (multiple time points) - SAS

[Jack_Smitherson]

Hi everyone, 

I am stuck on this problem, and I was hoping I can get some help on this. Let's there are 3 columns: 

Column A                   Column B         Column C

12/06/2015 0:00         1.2                    3.3

12/06/2015 1:00         4.5                    6.8

12/06/2015 2:00         0.2                    10

12/07/2015 13:00       9.9                    7.3

Please note that Column A has a date and time (in the fomrat you see above). I'm trying to create an output file with the MAXIMUM value for each day (there would be multiple values (i.e. hours) for each day, and I want to have the highest value for that day). 

How do I go about accomplishing that on SAS? Any help would be highly appreciated!

Cheers,

[Astounding]

If Column_A is truly a SAS date-time value, you could create a new variable holding just the day:

just_the_day = datepart(column_A);

Then use that new variable when summarizing the data.  However, looking at the values you have listed, it appears that Column_A is actually a character string that holds a date and time.  If that's the case, you can go right to the summarization stage:

proc summary data=have nway;

class Column_A;

format Column_A $10.;

var Column_B Column_C;

output out=maxvals (drop=_type_ _freq_) max=max_B max_C;

run;

Notice a quirk of the output data set MAXVALS.  The actual value of Column_A might include both a date and a time.  But the data still represents the maximum for the day even though a time is part of the value of Column_A.

[Ksharp]

data have;
input ColumnA  & anydtdtm.           ColumnB         ColumnC ;
format columna datetime20.;
cards;
12/06/2015 0:00         1.2                    3.3
12/06/2015 1:00         4.5                    6.8
12/06/2015 2:00         0.2                    10
12/07/2015 13:00       9.9                    7.3
;
run;
proc summary data=have nway;
class columna;
format columna dtdate9.;
var columnb columnc;
output out=want max=;
run;

[Jack_Smitherson]

Thanks for your help! 

How do I go about doing the same thing but instead, I'd like to use an excel document? I have a lot more columns and data that I'd like to figure out the maximum value for! 

Thanks again for your help.

[SuryaKiran]

Hi,

Import the dataset into SAS and then use proc summary as mentioned by @Ksharp.

proc import
datafile="path"
dbms=xlsx
out=work.Imported_Excel
replace;
run;

Mention all the variables that you need the max values in "VAR" statement. If you have too many variables then use dictionary.columns to find all the variabels and put them in a macro using INTO: and reference in "VAR" statemnt.

Thanks,

Suryakiran

[Jack_Smitherson]

Hi SuryaKiran,

When I import the file anddo the proc summary procedure, I get this message on SAS: " Variable columnb in list does not match type prescribed for this list."

Not sure why I'm getting this message... 

[ballardw]

---

@Jack_Smitherson wrote:

Hi SuryaKiran,

When I import the file anddo the proc summary procedure, I get this message on SAS: " Variable columnb in list does not match type prescribed for this list."

Not sure why I'm getting this message... 

---

Proc summary will only allow actual numeric variables on the VAR statement. So you have an issue with how ever you imported the data the variable columnb came in as character. This is a common problem when starting with Excel especially using Proc Import as the procedure has to guess a variable type from the first few rows of data and if the values are all missing will default to character or if the data is mixture, not an infrequent occurence with data entered into Excel.

Just because it looks like a number when viewed in Excel it is not necessarily so.

[SuryaKiran]

Something like this might work.

data have;
input ColumnA  & anydtdtm. ColumnB ColumnC ;
format columna datetime20.;
cards;
12/06/2015 0:00  1.2  3.3
12/06/2015 1:00  4.5  6.8
12/06/2015 2:00  0.2  10
12/07/2015 13:00  9.9  7.3
;
run;
/* If you have too may columns then make use of dictionary.columns */
PROC SQL;
SELECT name INTO: vars separated by " " from DICTIONARY.COLUMNS 
WHERE LIBNAME="WORK" AND MEMNAME="HAVE" AND TYPE="num" AND FORMAT<>"DATETIME20.";
/* Date type is also num so, excluding the date column by filtering for format */
quit;
proc summary data=have nway;
class columna;
format columna dtdate9.;
var &Vars. ;
output out=want max=;
run;

When you use proc import then check for the date format and make modification in the PROC SQL filter.

Thanks,

Suryakiran