BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Dear,

I have two datasets. One EX and other AE. I need create a variable called LASTDOSE in my AE dataset by comparing AEdate with exdate. Please help.


EX

Subid        exdate            exdose
1              07FEB2016          5
1             17FEB2016         10
1              27FEB2016        20
2             07JAN2016         20
2            17JAN2016          30
2           17JAN2016           40


AE

Subid                AEDATE
1                    18FEB2016
2                    11JAN2016

 

Output needed.

Subid                       AEDATE                           Lastdose
1                               18FEB2016                       10
2                              11JAN2016                         20

 

For SUBID=1 10 is the last dose taken before AEdate 
For SUBID=2 20 is the last dose taken before AEdate

 

 

5 REPLIES 5
stat_sas
Ammonite | Level 13

Hi,

 

Please try this.

 

proc sql;
create table want as
select a.*, exdose as Lastdose from ae a
inner join ex b
on a.subid=b.subid
group by a.subid
having abs(exdate-aedate)=min(abs(exdate-aedate));
quit;

knveraraju91
Barite | Level 11

This code works, but I need the last dose and  exdate less than aedate because that is the last dose taken before ae. Your code has ABS function which rounds to positive values and the exdate greater aedate also considered. PLease help

stat_sas
Ammonite | Level 13

Hi,

 

I've modified the code. Please see below:

 

proc sql;
create table want as
select a.*, exdose as Lastdose from ae a
inner join ex b
on a.subid=b.subid
and exdate<aedate  /* Restricting exdate before acdate */
group by a.subid
having aedate-exdate=min(aedate-exdate);
quit;

ballardw
Super User

Proc SQL is very useful for joining on conditions. The following code should do what you want:


proc sql;
   create table want as 
   select ae.subid, ae.aedate,ex.exdose as lastdose
   from ae left join 
        (select subid, exdose
         from ex 
         group by subid
         having exdate=max(exdate))
   ;
quit;

You will get a warning about "remerging statistics back with original data" because of the Having clause.

 

If there is no dose associated with the SUBID then the Lastdose variable will be missing.

Ksharp
Super User
data EX;
input Subid        exdate   : date9.         exdose;
format exdate    date9.;
cards;
1              07FEB2016          5
1             17FEB2016         10
1              27FEB2016        20
2             07JAN2016         20
2            17JAN2016          30
2           17JAN2016           40
;
run;

data AE;
input Subid                AEDATE : date9.;
format   AEDATE date9.;
cards;
1                    18FEB2016
2                    11JAN2016
;
run;
proc sql;
select a.*,b.exdose
 from AE as a left join EX as b 
  on a.Subid=b.Subid and a.AEDATE-b.exdate >0
    group by a.Subid,a.AEDATE 
     having a.AEDATE-b.exdate=min(a.AEDATE-b.exdate);
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 965 views
  • 3 likes
  • 4 in conversation