BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bkoksal
Calcite | Level 5

Dear SAS Users,

Here is a puzzle.

  I have a data set as follows:

symboldateXYZ
ABC1/2/20125..
ABC1/3/20127..
ABC1/4/20122..
ABC1/5/2012.20.
ABC1/6/2012...
ABC1/9/2012...
ABC1/10/20125..
ABC1/11/201211..
ABC1/12/201252
ABC1/13/20128..
DEF1/2/20125..
DEF1/3/2012715.
DEF1/4/20128..
DEF1/5/20126..
DEF1/6/2012..3
DEF1/9/20129..
DEF1/10/20125..
DEF1/11/2012..4
DEF1/12/2012...
DEF1/13/20129..

I want to produce a new variable newX such that

1. if Y^=. and X^=. then NewX=X+Y

2. if Z^=. and X^=. then NewX=X*Z

3. if Y^=. and X=. then the value of Y will be added to the next nonmissing X

4. if Z^=. and X=. then the value of Z will be multiplied by the next nonmissing X

The output will be as follows:

symboldateXYZNewX
ABC1/2/20125..5
ABC1/3/20127..7
ABC1/4/20122..2
ABC1/5/2012.20..
ABC1/6/2012....
ABC1/9/2012....
ABC1/10/20125..25
ABC1/11/201211..11
ABC1/12/20125210
ABC1/13/20128..8
DEF1/2/20125..5
DEF1/3/2012715.22
DEF1/4/20128..8
DEF1/5/20126..6
DEF1/6/2012..3.
DEF1/9/20129..27
DEF1/10/20125..5
DEF1/11/2012..4.
DEF1/12/2012....
DEF1/13/20129..36

How can this be done? Thanks for any help in advance.

INPUT DATA

symbol date X Y Z

ABC 1/2/2012 5 . .

ABC 1/3/2012 7 . .

ABC 1/4/2012 2 . .

ABC 1/5/2012 . 20 .

ABC 1/6/2012 . . .

ABC 1/9/2012 . . .

ABC 1/10/2012 5 . .

ABC 1/11/2012 11 . .

ABC 1/12/2012 5  2

ABC 1/13/2012 8 . .

DEF 1/2/2012 5 . .

DEF 1/3/2012 7 15 .

DEF 1/4/2012 8 . .

DEF 1/5/2012 6 . .

DEF 1/6/2012 . . 3

DEF 1/9/2012 9 . .

DEF 1/10/2012 5 . .

DEF 1/11/2012 . . 4

DEF 1/12/2012 . . .

DEF 1/13/2012 9 . .

OUTPUT DATA

symbol date X Y Z NewX

ABC 1/2/2012 5 . . 5

ABC 1/3/2012 7 . . 7

ABC 1/4/2012 2 . . 2

ABC 1/5/2012 . 20 . .

ABC 1/6/2012 . . . .

ABC 1/9/2012 . . . .

ABC 1/10/2012 5 . . 25

ABC 1/11/2012 11 . . 11

ABC 1/12/2012 5  2 10

ABC 1/13/2012 8 . . 8

DEF 1/2/2012 5 . . 5

DEF 1/3/2012 7 15 . 22

DEF 1/4/2012 8 . . 8

DEF 1/5/2012 6 . . 6

DEF 1/6/2012 . . 3 .

DEF 1/9/2012 9 . . 27

DEF 1/10/2012 5 . . 5

DEF 1/11/2012 . . 4 .

DEF 1/12/2012 . . . .

DEF 1/13/2012 9 . . 36

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Just like Tom has pointed out, your rules are ambiguous.  I will just give an example based on your data as is and there are implied rules such as:

1. y and z are mutually exclusive.

or

1. z rules triumph over y rules.

data have;

infile cards;

input symbol $ date :mmddyy10. X Y Z;

format date mmddyy10.;

cards;

ABC 1/2/2012 5 . .

ABC 1/3/2012 7 . .

ABC 1/4/2012 2 . .

ABC 1/5/2012 . 20 .

ABC 1/6/2012 . . .

ABC 1/9/2012 . . .

ABC 1/10/2012 5 . .

ABC 1/11/2012 11 . .

ABC 1/12/2012 5  2

ABC 1/13/2012 8 . .

DEF 1/2/2012 5 . .

DEF 1/3/2012 7 15 .

DEF 1/4/2012 8 . .

DEF 1/5/2012 6 . .

DEF 1/6/2012 . . 3

DEF 1/9/2012 9 . .

DEF 1/10/2012 5 . .

DEF 1/11/2012 . . 4

DEF 1/12/2012 . . .

DEF 1/13/2012 9 . .

;

data want (drop=_:);

retain _ry _rz;

set have;

_ry=ifn(missing(y),_ry,y);

_rz=ifn(missing(z),_rz,z);

if not missing(x) then

   do;

      if missing(_rz) then

         do;

            newx=sum(x,_ry);

call missing(_ry);

end;

else if not missing(_rz) then

          do;

            newx=x*_rz;

call missing (_rz);

           end;

end;

run;

proc print;run;

Kindly Regards,

Haikuo

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

Those conditions overlap.  You need to define what order you want the operations to happen.

If x=1 ,y=2 and z=4 then the first two rules imply that NewX is either 3 or 4 depending on which of the two rules to use.

Haikuo
Onyx | Level 15

Just like Tom has pointed out, your rules are ambiguous.  I will just give an example based on your data as is and there are implied rules such as:

1. y and z are mutually exclusive.

or

1. z rules triumph over y rules.

data have;

infile cards;

input symbol $ date :mmddyy10. X Y Z;

format date mmddyy10.;

cards;

ABC 1/2/2012 5 . .

ABC 1/3/2012 7 . .

ABC 1/4/2012 2 . .

ABC 1/5/2012 . 20 .

ABC 1/6/2012 . . .

ABC 1/9/2012 . . .

ABC 1/10/2012 5 . .

ABC 1/11/2012 11 . .

ABC 1/12/2012 5  2

ABC 1/13/2012 8 . .

DEF 1/2/2012 5 . .

DEF 1/3/2012 7 15 .

DEF 1/4/2012 8 . .

DEF 1/5/2012 6 . .

DEF 1/6/2012 . . 3

DEF 1/9/2012 9 . .

DEF 1/10/2012 5 . .

DEF 1/11/2012 . . 4

DEF 1/12/2012 . . .

DEF 1/13/2012 9 . .

;

data want (drop=_:);

retain _ry _rz;

set have;

_ry=ifn(missing(y),_ry,y);

_rz=ifn(missing(z),_rz,z);

if not missing(x) then

   do;

      if missing(_rz) then

         do;

            newx=sum(x,_ry);

call missing(_ry);

end;

else if not missing(_rz) then

          do;

            newx=x*_rz;

call missing (_rz);

           end;

end;

run;

proc print;run;

Kindly Regards,

Haikuo

bkoksal
Calcite | Level 5

Sorry for the confusion,

I thought it was clear from the example but apparently not. Y and Z are not nonmissing at the same time. In other words, if y is nonmissing, then Z is missing and vice versa.

So either we have the case

x=1, y=2 and z=.

or

x=1, y=. and z=4

bülent

Haikuo
Onyx | Level 15

If so, my code seems working for you. Have you tried on your real data?

Haikuo

bkoksal
Calcite | Level 5

Hi Haikuo,

My question was not about exactly what I needed for my data but I got the algorithm from your answer. After some slight modification of your code, it worked.

Thanks a lot.

bülent

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1281 views
  • 0 likes
  • 3 in conversation