Hi all,   I am trying to select the min of a date and then if it is null I would like to replace it with the value of another date field:     Select a.Acct ,a.EntryDte ,case b.PostDte when null then a.EntryDte else min(b.PostDte) end as PostDate   from tableA A left outer join tableB B on A.Nbr = B.Nbr group by a.Acct, a.EntryDte     while it is grouping the way I want (the b.PostDte has several values per account number), my desired field PostDate is still showing up as null and not the EntryDte (01APR2019) e.g.:   acct   EntryDte    PostDate 123    01APR2019    null 456    01APR2019    14MAR2019   Any insight is appreciated!   I have tried this in SAS EG with a proc SQL as well as an SQL program without any luck. ... View more

I am attempting to create a random sample using proc sql. I found this article from the SAS website that uses the function RANUNI ( the following paper ) however when I try to run this I get an error saying RANUNI "is not a recognized build-in function name. "   Am I using it incorrectly or is there another function I can use for this?   here is my code using SAS EG to try and get a 10% sample:   proc sql; connect to sqlsvrasfrprod(datasrc=frproduser=&uidpassword="&pawd" readbuff=3000); create table sample as select * from connection to frprod( select distinct a.startdate, a.notifydate, a.id, a.ctype, a.type, a.longdesc, b.account, c.ctypid from comp.dbo.comp a join comp.dbo.accounts b on a.id=b.id join comp.dbo.ban c on left(b.account,6)=c.bannumber and ctypid between 2 and 5 where ranuni(1234) between .45 and .55; ); quit;     ... View more

Hi All, I am trying to join two tables together, the first has a list of accounts(a) and the second has a list of associated merchants(b). As each account may be associated with the same merchant more than once, I would like to return a final table with distinct account numbers based on the min of the first date and max of last date fields in the associated merchant table. Have for a single account: a.ID_Key,       a.EntryDate                a.AccountNumber,     123              03JAN19:00:00:00        5678             b.Merch_ID_Key     b.Entry_Date                 b.First_Merch_Date             b.Last_Merch_Date     123                        03JAN19:00:00:00        21OCT18:00:00:00              22DEC18:00:00:00     123                        03JAN19:00:00:00        01MAR16:00:00:00              20SEP17:00:00:00     Want: a.ID_Key,       a.EntryDate                a.AccountNumber,    b.First_Merch_Date             b.Last_Merch_Date    123              03JAN19:00:00:00        5678                        01MAR16:00:00:00              22DEC18:00:00:00   My output is giving me the correct amount of rows but the dates are coming back as "*******"     Here is my query:   proc sql; create table MerchAcct as select a.ID_Key, a.EntryDate, a.AccountNumber, min(b.First_Merch_Date) as First_Merch_Date format date9., max(b.Last_Merch_Date) as Last_Merch_Date format date9. from Accounts a join Merch b on a.ID_Key=b.ID_Key and a.EntryDate=b.EntryDate group by a.ID_Key, a.EntryDate, a. AccountNumber; quit;   As a reference I am using SAS EG. ... View more