## Create a new variable and make it equal to other corresponding variables

Solved
Frequent Contributor
Posts: 130

# Create a new variable and make it equal to other corresponding variables

I would like to create a new variable "period_all", and make each of its value equals to corresponding value of period_1 to period_9 (as the table below shows).

The following macro only create the last value of period_all (which equals to the value of period_9), but others are missing values.

I tried to add "retain" and "output" statements in the macro, still doesn't work.

Thanks!

%macro newvariable;

%do i=1 %to 9;

data want;

set have;

if period_&i ^=. then period_all=period_&i;

run;

%end;

%mend;

%newvariable;

Accepted Solutions
Solution
‎07-17-2014 09:03 PM
Super Contributor
Posts: 312

## Re: Create a new variable and make it equal to other corresponding variables

You could also use an array.

DATA WANT1;

SET HAVE;

ARRAY _PERIOD PERIOD_1-PERIOD_9;

I = 0;

DO WHILE (PERIOD_ALL = .);

I = I+1;

PERIOD_ALL = _PERIOD{I};

END;

DROP I;

RUN;

All Replies
Super Contributor
Posts: 312

## Re: Create a new variable and make it equal to other corresponding variables

Hi Jonate,

Is the attached sample representative of your data?

If so you could add all the variables together to get the value in period_all.

DATA WANT;

SET HAVE;

PERIOD_ALL = SUM(OF PERIOD_1-PERIOD_9);

RUN;

Regards,

Scott

Solution
‎07-17-2014 09:03 PM
Super Contributor
Posts: 312

## Re: Create a new variable and make it equal to other corresponding variables

You could also use an array.

DATA WANT1;

SET HAVE;

ARRAY _PERIOD PERIOD_1-PERIOD_9;

I = 0;

DO WHILE (PERIOD_ALL = .);

I = I+1;

PERIOD_ALL = _PERIOD{I};

END;

DROP I;

RUN;

Frequent Contributor
Posts: 130

## Re: Create a new variable and make it equal to other corresponding variables

Thank you, Scott! That works.

SAS Employee
Posts: 1

## Re: Create a new variable and make it equal to other corresponding variables

Hi Jonate,

Although Scott's answer is good enough, I assume you might want to know why your code is not working. In your code, your are running data step 9 times. If you run the macro, SAS will run

data want;

set have;

if period_1 ^=. then period_all=period_1;

run;

data want;

set have;

if period_2 ^=. then period_all=period_2;

run;

...

data want;

set have;

if period_9 ^=. then period_all=period_9;

run;

I assume this is not what you want.

Try the following macro instead (although inefficient),

%macro newvariable;

data want;

set have;

%do i=1 %to 9;

if period_&i ^=. then period_all=period_&i;

%end;

run;

%mend newvariable;

This will write

data want;

set have;

if period_1 ^=. then period_all=period_1;

if period_2 ^=. then period_all=period_2;

...

if period_9 ^=. then period_all=period_9;

run;

I hope this illustrates how the macro works.

Frequent Contributor
Posts: 130

Thank you, Bong!

Super User
Posts: 8,127

## Re: Create a new variable and make it equal to other corresponding variables

If you really have that type of matrix with values only on the diagonal then the COALESCE() function will do what you want.

data want ;

set have ;

period_all = coalesce(of period_1-period_9) ;

run;

Frequent Contributor
Posts: 130

## Re: Create a new variable and make it equal to other corresponding variables

Thank you, Tom!

🔒 This topic is solved and locked.