Creating a new variable while SQL variable extraction ?

Mirisage — Mon, 21 Apr 2014 20:01:49 GMT

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

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

Re: Creating a new variable while SQL variable extraction ?

DBailey — Mon, 21 Apr 2014 20:04:42 GMT

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

a.patid=b.patid;

quit;

%mend;

Re: Creating a new variable while SQL variable extraction ?

Mirisage — Tue, 22 Apr 2014 19:21:53 GMT

Hi DBailey,

Thank you very much for your suggestion which worked well.

Regards

Mirisa

topic Creating a new variable while SQL variable extraction ? in SAS Procedures

Creating a new variable while SQL variable extraction ?

Re: Creating a new variable while SQL variable extraction ?

Re: Creating a new variable while SQL variable extraction ?