topic Re: How to add a statement to pull max date in New SAS User

How to add a statement to pull max date

squeakums — Fri, 30 Sep 2022 17:57:19 GMT

How would I write in the code to pull the max Open date in this code? Also, I think I'm writing this correct. I get confused about the joines. But, the ID file has all of the ID's and the ITA file doesn't but I want to pull in the details to the ID file. Is an inner join the correct way to do this or should it be a left join, center or right join?

Proc Sql;
Create Table A As
Select Distinct
A.'ID'n,
B.'ITA ID'n,
B.'Open Date'n,
B.'Due Date'n,
B.'Status'n,
B.'Completion Date'n
From FolderName.ID as A
inner join FolderName.ITA as B
on A.'ID'n = B.'ID'n
Order by 'ID'n
;
Quit

Re: How to add a statement to pull max date

svh — Fri, 30 Sep 2022 18:55:51 GMT

If you want to keep all of the observations that are in the table ID. then use LEFT JOIN

As to your other question, you could try to add this to your select statement (enclosed in commas)
max(B.'Open Date'n) as Max_date

Re: How to add a statement to pull max date

squeakums — Fri, 30 Sep 2022 19:27:41 GMT

How would I write that in my code exactly? This is not working unfortunately:

Proc Sql;
Create Table A As
Select Distinct
A.'ID'n,
B.'ITA ID'n,
B. max('Approval Date'n) as 'LatestApprovalDate'n,
B.'Due Date'n,
B.'Status'n,
B.'Completion Date'n
From FolderName.ID as A
inner join FolderName.ITA as B
on A.'ID'n = B.'ID'n
Order by 'ID'n
;
Quit

Re: How to add a statement to pull max date

svh — Fri, 30 Sep 2022 19:36:31 GMT

I think the B. is in the wrong place. Try this:

max(B.'Approval_Date'n) as max_date

Re: How to add a statement to pull max date

squeakums — Fri, 30 Sep 2022 19:45:01 GMT

Okay, I updated this and it ran but it isn't pulling in dates - instead its pulling in 22914 for each row. Also, it still have dups for the ID.

ID	ITA	LatestApprovalDate
12345		22914
12345	ITA-125	22914
12345	ITA-127	22914
12345	ITA-187	22914
12345	ITA-90	22914
15434	ITA-234	22914

Re: How to add a statement to pull max date

Kurt_Bremser — Fri, 30 Sep 2022 22:04:54 GMT

This is the correct value, but it is not yet formatted to become human-readable.

Add a FORMAT= option with a proper date format to the column definition.

Re: How to add a statement to pull max date

Tom — Sat, 01 Oct 2022 06:36:08 GMT

The number 22,914 is the value SAS uses for the 26th of September of 2022.

If you want SAS to print it in a more human friendly way then attach a numeric format that is designed to print date values to the new calculated variable. Like DATE9. or YYMMDD10.

5    data _null_;
6      date=22914;
7      put date= date9.;
8    run;

date=26SEP2022

You have multiple observations per ID because you selected extra variables (variables that are neither a grouping variable nor the result of an aggregate function).

If you don't want the extra observations do not include the extra variables in the column list part of the SELECT statement.

create table WANT as
select
  A.ID
, max(B.'Approval Date'n) as LatestApprovalDate format=date9.
from HAVE1 A
inner join HAVE2 B
on A.ID = B.ID
group by A.ID
;

PS It will be much easier to read and write your code if you set the VALIDVARNAME option to V7 instead of ANY. That way SAS will prevent you from making variable names, like your examples with the spaces in them, that require the use of the cumbersome name literals in the code.