topic Re: Finding the lowest date in a column in New SAS User

Finding the lowest date in a column

Schmageggy — Thu, 16 May 2019 20:09:40 GMT

Hi, I need to find the lowest value in a date column, but i'm not entirely sure how to go about it.

Sample data attached.

I'm new to this, but I tried using a min function, that didn't work.

Tried creating a case to cover it, but that didn't either, any help is appreciated.

Re: Finding the lowest date in a column

PaigeMiller — Thu, 16 May 2019 20:11:34 GMT

You can use PROC MEANS or PROC SUMMARY and have it compute the MIN of the column of interest.

The MIN function (which you tried) finds the minimum across a row.

Re: Finding the lowest date in a column

Kurt_Bremser — Thu, 16 May 2019 20:20:10 GMT

proc sql;
select min(date) from have;
quit;

Replace dataset and column name as needed.

Re: Finding the lowest date in a column

Reeza — Thu, 16 May 2019 20:53:51 GMT

What do you want to do with the minimum after you find it?

The approaches here may help, just replace mean with min.
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

Re: Finding the lowest date in a column

ballardw — Thu, 16 May 2019 22:53:59 GMT

@Schmageggy wrote:

Hi, I need to find the lowest value in a date column, but i'm not entirely sure how to go about it.

Sample data attached.

I'm new to this, but I tried using a min function, that didn't work.

Tried creating a case to cover it, but that didn't either, any help is appreciated.

Didn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

XLSX are not SAS data sets. Any choice I make to turn such into a SAS data set may result in differences if variable type, values (especially with dates, times or datetime values), and variable lengths from your SAS data.

Re: Finding the lowest date in a column

Schmageggy — Thu, 16 May 2019 23:15:41 GMT

I'm trying to make a computed column with the lowest date so then I can run a select distinct query excluding the original column and result in one date per record.

Re: Finding the lowest date in a column

Patrick — Fri, 17 May 2019 23:45:45 GMT

@Schmageggy

If you want to select the row with the lowest datetime value within a group then code as below should do the job.

data have;
  do record='Record1', 'Record2';
    do othervar=0 to 100000 by 10000;
      dttm=datetime()-othervar;
      output;
    end;
  end;
  stop;
  format dttm datetime20.;
run;

proc sql;
/*  create table want as*/
  select
    record,
    othervar,
    min(dttm) as min_dttm format=datetime20.
  from have
  group by record
  having min(dttm)=dttm
  ;
quit;

The SQL above is something you can "click together" using the EG Query Builder.

Once you create the calculated column "min_dttm" you will get additional options to chose from.

The GROUP BY in above SQL gets created based on what you define in pane "Summary Groups"

The HAVING clause gets created based on what you define under tab "Filter Data"/"Filter the summarized data"

Correction:

The having clause was: having max(dttm)=dttm

The test must use min() of course: having min(dttm)=dttm

Fixed now.

Re: Finding the lowest date in a column

Schmageggy — Fri, 17 May 2019 15:23:15 GMT

That did it, thank you. But yeah I am using EG as SQL while not completely foreign, I have not learned or been trained in my current role. Should probably work on that. Thank you again.

Re: Finding the lowest date in a column

Reeza — Fri, 17 May 2019 20:10:45 GMT

SAS has two free e courses available to get you started.