Creating a new variable while SQL variable extraction ?

[Mirisage]

/*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

[DBailey · Accepted solution]

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;

[Mirisage]

Hi DBailey,

Thank you very much for your suggestion which worked well.

Regards

Mirisa