Help using Base SAS procedures

Creating a new variable while SQL variable extraction ?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

Creating a new variable while SQL variable extraction ?

/*Hi SAS Forum,

I have the table named "one"*/

data one;

input trt patid visit dose;

cards;

0 101 1 10

0 101 2 20

1 102 1 15

1 102 2 20

;

run;

/*I wanted to extract the field "trt" from above

table and at the same time I wanted to create a brand new variable named "which_year".  The values of field "which_year" should get 1, 2 and 3 in the three tables

being created. Below macro does the

job correctly.*/

%macro any (year_number);

proc sql;

    create table Year_&year_number as

                select          trt,

                                &year_number as which_year

                

     from    one         

     ;

quit;

%mend;

%any (1);

%any (2);

%any (3);

/*Now I have two tables like below and wanted to do a left join and wanted to do the

same thing above   */

data one;

input trt patid visit dose;

cards;

0 101 1 10

0 101 2 20

1 102 1 15

1 102 2 20

;

run;

data two;

input patid sex $ 5-6 ;

cards;

101 F

102 M

103 M

105 F

;

run;

/*Below code does the job correctly */

%macro any (year_number);

proc sql;

    create table Year_&year_number as select

                a.*  ,

                b.sex

                

     from    one              a left join

             two              b

                on

                a.patid=b.patid;

quit;

%mend;

%any (1);

%any (2);

%any (3);

Question:

In the yellow highlighted table above, can’t I create a brand new variable named "which_year" which has values 1, 2 and 3 in the three tables being created.?

Thanks

Mirisa


Accepted Solutions
Solution
‎04-21-2014 04:04 PM
Super Contributor
Posts: 578

Re: Creating a new variable while SQL variable extraction ?

Wouldn't it just be:

%macro any (year_number);

proc sql;

    create table Year_&year_number as select

                a.*  ,

       b.sex ,

      &year_number as which_year

     from    one              a left join

             two              b

                on

                a.patid=b.patid;

quit;

%mend;

View solution in original post


All Replies
Solution
‎04-21-2014 04:04 PM
Super Contributor
Posts: 578

Re: Creating a new variable while SQL variable extraction ?

Wouldn't it just be:

%macro any (year_number);

proc sql;

    create table Year_&year_number as select

                a.*  ,

       b.sex ,

      &year_number as which_year

     from    one              a left join

             two              b

                on

                a.patid=b.patid;

quit;

%mend;

Super Contributor
Posts: 338

Re: Creating a new variable while SQL variable extraction ?

Hi DBailey,

Thank you very much for your suggestion which worked well.

Regards

Mirisa

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 227 views
  • 0 likes
  • 2 in conversation