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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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