DATA Step, Macro, Functions and more

Assign Variable in PROC SQL

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Assign Variable in PROC SQL

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;


Accepted Solutions
Solution
‎12-14-2012 11:19 AM
Frequent Contributor
Posts: 127

Re: Assign Variable in PROC SQL

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;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Assign Variable in PROC SQL

other way around, 'XXXX' as drug

Solution
‎12-14-2012 11:19 AM
Frequent Contributor
Posts: 127

Re: Assign Variable in PROC SQL

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;

Regular Contributor
Posts: 220

Re: Assign Variable in PROC SQL

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 239 views
  • 3 likes
  • 3 in conversation