Hi,
I have the following SQL code; I am trying to write the data step code similar to this. I tried some thing but in vain.Could any one help me?
I could not understand the code highlighted in bold.
Please find the sql code below.
proc sql;
create table dem1 as
select ex.*,
case when ex.dafl is null then adverse.dafl else ex.dafl end as date1,
case when ex.tfl is null then adverse.tfl else ex.tfl end as time1,
case when ex.pidno is null then adverse.pidno else ex.pidno end as acc1,
case when ex.folder is null then adverse.folder else ex.folder end as lbn1
from dem ex
left join
(select t1.usubjid,t1.visitnum,rsn,t1.subev,t1.visitd,t2.dafl,t2.tfl,t2.pidno,t2.folder
from
(select usubjid,visitnum,visitd,rsn,subev from dem where rsn>1) t1
join (select usubjid,visitnum,visitd,dafl,tfl,subev,pidno,folder from dem where rsn=1) t2
on t1.usubjid = t2.usubjid and t1.visitnum = t2.visitnum and t1.visitd = t2.visitd and t1.subev = t2.subev) adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
Following is my Data step code:
data t1 (keep= usubjid visitnum visitd rsn subev);
set dem ;
where rsn>1 ;
run;
data t2 (keep= usubjid visitnum visitd dafl tfl subev pidno folder);
set dem ;
where rsn=1 ;
run;
proc sort data=t1; by usubjid visitnum visitd subev; run;
proc sort data=t2; by usubjid visitnum visitd subev; run;
data adverse;
merge t1 (in=a) t2 (in=b);
by usubjid visitnum visitd subev;
if a and b;
run;
proc sort data=ex; by usubjid visitnum visitd rsn subev; run;
proc sort data= adverse; by usubjid visitnum visitd rsn subev; run;
data ex ;
merge ex (in=a) adverse (in=b);
by usubjid visitnum visitd rsn subev;
if a;
run;
data dem1 ;
set dem ;
if ex.dafl eq “ “ then date1=adverse.dafl;
else then date1=ex.dafl;
if ex.tfl eq “ “ then time1= adverse.tfl;
else then time1= ex.tfl;
if ex.pidno eq “ “ then acc1= adverse.pidno;
else then acc1= ex.pidno;
if ex.folder eq “ “ then lbn1= adverse.folder;
else then lbn1= ex.folder;
run;
Thank Patrick.
1) I was wondering if we can modify the above code to remove ex completely? (i.e how can we can modify the above code without the use of any alias?
2) In the last step if we use keep, drop or rename the variables, how is it possible to do merging? (as are doing the merging the by common variables).
The select statement refers to the inline view bit.
(select t1.usubjid,t1.visitnum,rsn,t1.subev,t1.visitd,t2.dafl,t2.tfl,t2.pidno,t2.folder
from
(select usubjid,visitnum,visitd,rsn,subev from dem where rsn>1) t1
join (select usubjid,visitnum,visitd,dafl,tfl,subev,pidno,folder from dem where rsn=1) t2
on t1.usubjid = t2.usubjid and t1.visitnum = t2.visitnum and t1.visitd = t2.visitd and t1.subev = t2.subev) adverse
So from "outside" the join looks then like below:
proc sql;
create table dem1 as
select ex.*,
case when ex.dafl is null then adverse.dafl else ex.dafl end as date1,
case when ex.tfl is null then adverse.tfl else ex.tfl end as time1,
case when ex.pidno is null then adverse.pidno else ex.pidno end as acc1,
case when ex.folder is null then adverse.folder else ex.folder end as lbn1
from dem ex
left join
adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
Why do you want to convert this into a data step?
I believe your last step would be:
data ex ;
merge ex (in=a) adverse (in=b);
by usubjid visitnum visitd rsn subev;
if a;
.... and here some if statements to replace the SQL Case statements...
run;
Thank you very much Patrick.
proc sql;
create table dem1 as
select ex.*,
case when ex.dafl is null then adverse.dafl else ex.dafl end as date1,
case when ex.tfl is null then adverse.tfl else ex.tfl end as time1,
case when ex.pidno is null then adverse.pidno else ex.pidno end as acc1,
case when ex.folder is null then adverse.folder else ex.folder end as lbn1
from dem ex
left join
adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
I have some questions in the above code.
1) why are the dem ex lying side by side in the following code. What is the meaning of following code.
"from dem ex
left join
adverse?
2) I was wondering if the left join in between dem & adverse or ex & adverse?
3) What is going in to dem1? Is it the product of left join? If so why does the code have "select ex* "
4) I believe my last step would be the following code based on your suggestion.
data ex ;
merge ex (in=a) adverse (in=b);
by usubjid visitnum visitd rsn subev;
if a;
if ex.dafl eq “ “ then date1=adverse.dafl;
else then date1=ex.dafl;
if ex.tfl eq “ “ then time1= adverse.tfl;
else then time1= ex.tfl;
if ex.pidno eq “ “ then acc1= adverse.pidno;
else then acc1= ex.pidno;
if ex.folder eq “ “ then lbn1= adverse.folder;
else then lbn1= ex.folder;
run;
I always learned SQL by comparing with data step code. I could not understand the sql code that I posted here (especially the one highlighted in bold). That's why I am trying to convert this into a data step.
Thank you very much for you support.
Think of this SQL this way:
proc sql;
create view adverse as
select t1.usubjid,t1.visitnum,rsn,t1.subev,t1.visitd,t2.dafl,t2.tfl,t2.pidno,t2.folder
from
(select usubjid,visitnum,visitd,rsn,subev from dem where rsn>1) t1
join (select usubjid,visitnum,visitd,dafl,tfl,subev,pidno,folder from dem where rsn=1) t2
on t1.usubjid = t2.usubjid and t1.visitnum = t2.visitnum and t1.visitd = t2.visitd and t1.subev = t2.subev
;
quit;
proc sql;
create table dem1 as
select ex.*,
case
when ex.dafl is null then adverse.dafl
else ex.dafl
end
as date1,
case
when ex.tfl is null then adverse.tfl
else ex.tfl
end
as time1,
case
when ex.pidno is null then adverse.pidno
else ex.pidno
end
as acc1,
case
when ex.folder is null then adverse.folder
else ex.folder
end
as lbn1
from dem ex
left join
adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
In the original SQL you have an inline view defined, like: (.....) adverse
adverse is an alias for this inline view allowing you to reference it in your code. In the "outside" SQL you're then using this inline view as if it were a view defined in an earlier SQL (as done here in this post).
A construct like "from dem ex" is simply: "dem" = table name, "ex" = alias. An alias is nothing else than a placeholder for the actual table or view name so you can use it in the rest of your code. You could also use the actual table name instead.
Your last data step should create table dem1. It looks close to what you need but has still a few syntax errors in it, eg. SAS data step syntax doesn't use 2 level names like "ex.dafl". It must be "dafl" only and if the variable exists in both input data set then you need to control from which dataset it should be taken (either using a keep, drop or rename).
Thank Patrick.
1) I was wondering if we can modify the above code to remove ex completely? (i.e how can we can modify the above code without the use of any alias?
2) In the last step if we use keep, drop or rename the variables, how is it possible to do merging? (as are doing the merging the by common variables).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.