proc sql: combine data with string dates

[Miracle]

Dear All,
Can I please seek your help on how to combine this 2 proc sql together?
The first proc sql is to obtaine the date of first and last treatment.

The second proc sql is to identify treatment emergent AE.
Instead of two separate proc sql, how can I achieve this by using one proc sql?
Thanks a lot.

proc sql;
	create table txdat as 
	select id, 
	       min(input(ECSTD,yymmdd10.)) format=is8601da. length=8 as trtstd,
				 max(input(ECEND,yymmdd10.)) format=is8601da. length=8 as trtend
	from ec
	group by id;
quit;
proc sql;
	create table chk as 
	select coalesce (x.id,y.id) as id, 
				 x.trtstd, 
				 y.AEYN,
				 y.aeterm, 
				 case when AESTDT <> "" then input(y.AESTDT,is8601da.) else . end as aestd format=is8601da. length=8,
				 case when AESTDT <> "" and trtstd <> . and calculated aestd >= trtstd then 1 else 0 end as teae,
				 y.tmp
	from txdat as x
	full join ae as y
	on x.id=y.id;
quit;

[Patrick · Accepted solution]

There might be more performant ways by re-formulating your SQL query but one quick way is to just copy/paste your 1st SQL to your 2nd SQL. This is basically replacing the table used in your 2nd SQL (created in your first SQL) with an inline-view.

proc sql;
	create table chk as 
	select coalesce (x.id,y.id) as id, 
				 x.trtstd, 
				 y.AEYN,
				 y.aeterm, 
				 case when AESTDT <> "" then input(y.AESTDT,is8601da.) else . end as aestd format=is8601da. length=8,
				 case when AESTDT <> "" and trtstd <> . and calculated aestd >= trtstd then 1 else 0 end as teae,
				 y.tmp
	from 
    (
    	select id, 
    	       min(input(ECSTD,yymmdd10.)) format=is8601da. length=8 as trtstd,
    				 max(input(ECEND,yymmdd10.)) format=is8601da. length=8 as trtend
    	from ec
    	group by id
    ) as x
	full join ae as y
	on x.id=y.id;
quit;

Another option which I prefer if all the data is within SAS (not connecting to a database) is to just create a view with the first SQL. This is from a performance perspective the same like using an inline view but it's easier to debug and also to read the code imho. So here the only change to your code would be a create View instead of a create Table in your first SQL.

proc sql;
	create view txdat as 
	select id, 
	       min(input(ECSTD,yymmdd10.)) format=is8601da. length=8 as trtstd,
				 max(input(ECEND,yymmdd10.)) format=is8601da. length=8 as trtend
	from ec
	group by id;
quit;
proc sql;
	create table chk as 
	select coalesce (x.id,y.id) as id, 
				 x.trtstd, 
				 y.AEYN,
				 y.aeterm, 
				 case when AESTDT <> "" then input(y.AESTDT,is8601da.) else . end as aestd format=is8601da. length=8,
				 case when AESTDT <> "" and trtstd <> . and calculated aestd >= trtstd then 1 else 0 end as teae,
				 y.tmp
	from txdat as x
	full join ae as y
	on x.id=y.id;
quit;

And last but not least: If going for an approach with two SQL's with the first one being a view it's often helpful during development or for debugging to create a table in the first SQL. But then when changing the table to a view in the same SAS session SAS will throw an error as it can't replace a view with a table (or vice versa). To overcome this obstacle I often add the following code on top of the SQLs:

proc datasets lib=work memtype=(data view) nolist nowarn;
  delete txtdat;
  run;
quit;

Because of using option NOWARN this proc datasets won't throw a warning if there isn't any table or view with the name txtdat so all good also when running the code the first time in a new SAS session.

[Miracle]

Thanks a lot @Patrick.

It's a very detailed explanation for someone who isn't a sql person.
I really appreciate it.

[Miracle]

Dear @Patrick  and All,
I have another question please.
How do I combine the demographic dataset that contains more patients to this sql please? So every patient from the demographic dataset will show in the final dataset regardless whether they are present in x or y dataset.

Thanks a lot. Again!

[Patrick]

You can add a left join to the SQL and then use coalesce() in the Select for any column that could come from more than one table.