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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.