Hi all,
I have 2 datasets: b is the main dataset, a contains the list of relevant unique personIDs that I want to pull data from b based on. In particular, I'm pulling the minimum date grouped by personID. I also want to restrict the minimum date for class "A" persons only and exclude missing dates.
Here is trouble: in the table I want to list (alongside personID and minimum date) the subclass associated with the minimum date. My table should have unique personIDs that look something like this:
personID FirstDate FirstDateSubClass
However, the table that I got contains duplicate personIDs even though it returned the minimum date correctly. I think SAS first subset dataset b using the "having" statement, then displays the min date for however many lines there are remaining in the b dataset as opposed to grouping them to one line per personID.
Here is the code I have so far:
*******
proc sql;
create table mytable as
select
a.personID,
b.personID,
min(b.date) as firstdate,
b.subclass as firstdatesubclass
from
a left join
b
on a.personID = b.personID
group by b.personID
having b.date ne . and b.class = 'A';
quit;
*************
How do I correct this code to return one unique personID per line containing minimum date of that personID and the subclass of that minimum date?
I have 2 datasets: b is the main dataset, a contains the list of relevant unique personIDs that I want to pull data from b based on. In particular, I'm pulling the minimum date grouped by personID. I also want to restrict the minimum date for class "A" persons only and exclude missing dates.
Here is trouble: in the table I want to list (alongside personID and minimum date) the subclass associated with the minimum date. My table should have unique personIDs that look something like this:
personID FirstDate FirstDateSubClass
However, the table that I got contains duplicate personIDs even though it returned the minimum date correctly. I think SAS first subset dataset b using the "having" statement, then displays the min date for however many lines there are remaining in the b dataset as opposed to grouping them to one line per personID.
Here is the code I have so far:
*******
proc sql;
create table mytable as
select
a.personID,
b.personID,
min(b.date) as firstdate,
b.subclass as firstdatesubclass
from
a left join
b
on a.personID = b.personID
group by b.personID
having b.date ne . and b.class = 'A';
quit;
*************
How do I correct this code to return one unique personID per line containing minimum date of that personID and the subclass of that minimum date?