Assign Variable in PROC SQL

[djbateman]

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;

[Reeza]

other way around, 'XXXX' as drug

[Florent · Accepted solution]

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;

[djbateman]

I knew it would be an easy fix.  Thank you both for your help!