Urgent help with PROC SQL DELETE query is needed 1

[RinaGreen]

Hi Everybody,
I have a problem with SQL PROC DELETE query...
After I successfully created table Denlab I need to eliminate partly duplicated records in order to leave just those ones with the same ID who are the most recent
However those ones who have just one record shold not be touched. Those ones whose date are the same should not be touched either

Being new in SAS,I am trying to implement it as if I do it in Access but it looks like SAS does not accept alliaces in my DELETE query.
Also, since the required field criteria has a prefix bb.... I am totally confused as it looks like my SQL is in conflict with SAS syntax.

How should I implement my DELETE query in SAS?
Could you please give me a hand?
Thank You in advance,

Rina
*****************************************************************************************

libname d 'c:\MyFolder\SAS Datasets';

proc sql;
create table d.DenLab as
select bb.id,mc.id, bb.dt_day,ll.value_start,bb.rslt
FROM d.combineBothTbls mc
join D.Lab bb
on mc.id = bb.id
join D.mycct ll
on ll.Value_Start = bb.RSLT
WHERE DATEPART(bb.dt_day) GE '01Jan2004'd
AND DATEPART(bb.dt_day) LE '31Dec2004'd;
run; /*this is done succesfully*/


/* need to delete just those ones whith the same id who is the most recent under bb.dt_day*/

proc sql;

DELETE *
FROM DenLab a, DenLab b
WHERE a.[bb.dt_day] Is Not Null AND Int([bb.dt_day])<(SELECT Max(Int([bb.dt_day])) FROM b WHERE b.[id]=a.[id]);

run;

 

[RinaGreen]

One more note...
Actually I realized that I can create a copy of the dataset instead aliases to use it in my DELETE query...


data d.DenLab2;
set d.denlab;
run;

proc sql;

DELETE *
FROM d.DenLab
WHERE d.DenLab.[bb.dt_day] Is Not Null AND Int([bb.dt_day])<(SELECT Max(Int([bb.dt_day]))
FROM d.DenLab2
WHERE DenLab2.[id]=DenLab[id]);

run;

but still a have the log pointing to the second dot (?) or when i take it off it points to square bracket[ :
d.DenLab.[bb.dt_day]
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=,
|, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

How can I overcome it?

 

[klaz2002]

Rina,
I don't understand your question, if a record has the same date isn't it a duplicate record?
The reason I ask is to eliminate duplicate records you have to tell sas the unique key that it should look for. for example lets say I had x=1 and y=1 in four records I can have sas eliminate the duplicate records by just using the nodupkey option in a proc sort. I believe that after you create your table you need to think of a unique key (this can be several variables too)that defines your record as unique and use it to eliminate dups.

I hope that I have shed some light on the issue.
Klaz

 

[RinaGreen]

Klaz,

Actually I found the way!!! Rather tan using delete query I used select and I got recent date.

proc sort data=d.denlab;
by id dt_dy
run:

data d.recent;
set d.denlab;
by id dt_day;
if last.id;
run;

HOWEVER I do not know how to get an appropriate format of the date mm/dd/yyyy
Instead I get 1402963200
1408406400
etc.
How can I get the format I am looking for?

Actually in my initial dataset(denlab) I also had 12Jan2004:00:00:00

Thank You,

Rina

 

[klaz2002]

Rina,
You need to use the datepart function. The dates that you have are datetime values (the number of seconds since 1/1/1960). The datepart() function will return the number of days since 1/1/1960. Then you use a simple format to get the date to look like the one you want. Here is an example:
ex.
data d.recent;
set d.denlab;
by id dt_day;
new_date = datepart(dt_day);
if last.id;
format new_date mmddyy10.;
run;

I hope that this helps you,
Klaz