This is a simple question with probably a very simple fix. I am just not sure how to do it.
I want to capture some data using PROC SQL and then assign a variable with all the same value. The example below is trying to assign an identifying variable DRUG that will let me know which drug a patient received when I stack all the data together. Any suggestions? Currently, drug as 'XXXXX' is not doing the trick.
/*** first dose dates ***/
proc sql;
create table ec225dates as
select unique patno, drug as 'EC0225', min(admindt) as firstdosedt format date9.
from ec225.ec225adm
group by patno;
create table ec489dates as
select unique patno, drug as 'EC0489', min(admindt) as firstdosedt format date9.
from ec489.ec0489
group by patno;
create table ec145dates as
select unique patno, drug as 'EC145', min(admindt) as firstdosedt format date9.
from fv01.ec145adm
group by patno;
quit;
data dosedates;
set ec225dates ec489dates ec145dates;
run;
Hi,
Just an thought: wouldn't you have inverted the 'drug' and the constants ('EC0225', 'EC0489', 'EC145') ? In this case, the 'unique' keywork would be useless as you are using a group by in all of your select statements.
Regards,
Florent
/*** first dose dates ***/
proc sql;
create table ec225dates as
select patno, 'EC0225' as drug, min(admindt) as firstdosedt format date9.
from ec225.ec225adm
group by patno;
create table ec489dates as
select patno, 'EC0489' as drug, min(admindt) as firstdosedt format date9.
from ec489.ec0489
group by patno;
create table ec145dates as
select patno, 'EC145' as drug, min(admindt) as firstdosedt format date9.
from fv01.ec145adm
group by patno;
quit;
data dosedates;
set ec225dates ec489dates ec145dates;
run;
other way around, 'XXXX' as drug
Hi,
Just an thought: wouldn't you have inverted the 'drug' and the constants ('EC0225', 'EC0489', 'EC145') ? In this case, the 'unique' keywork would be useless as you are using a group by in all of your select statements.
Regards,
Florent
/*** first dose dates ***/
proc sql;
create table ec225dates as
select patno, 'EC0225' as drug, min(admindt) as firstdosedt format date9.
from ec225.ec225adm
group by patno;
create table ec489dates as
select patno, 'EC0489' as drug, min(admindt) as firstdosedt format date9.
from ec489.ec0489
group by patno;
create table ec145dates as
select patno, 'EC145' as drug, min(admindt) as firstdosedt format date9.
from fv01.ec145adm
group by patno;
quit;
data dosedates;
set ec225dates ec489dates ec145dates;
run;
I knew it would be an easy fix. Thank you both for your help!
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 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.