BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Miracle
Barite | Level 11

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

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
Barite | Level 11

Thanks a lot @Patrick.

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

Miracle
Barite | Level 11

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
Opal | Level 21

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.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 613 views
  • 1 like
  • 2 in conversation