DATA Step, Macro, Functions and more

If Then Else statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

If Then Else statement

Hi All,

 

I'm trying to write a data step with IF THEN ELSE statements.

 

I have 2 columns and I'm trying to create a third column based on these two columns.

table:

column 1   column2   new_column 

A                    1                  X

A                    4                   X

A                    5                  X

B                    2                   Y

B                    3                    Y

B                    8                    Y

C                    1                    X,Y

C                    2                   X,Y

C                    7                     X,Y

 

IF a value in column 1 has value '1' in column 2 then the new column should be X and all the other rows should be X related to that column1 value A.

 

IF a value in column 1 has value '2' in column 2 then the new column should be Y and all the other rows should be Y related to that column1 value B.

 

IF a value in column 1 has value ('1','2') in column 2 then the new column should be (X,Y) and all the other rows should be X related to that column1 value C.

 

Could you help me with the syntax for this logic. The data set I'm working on had more than 300,000 rows. Thank you.

 

 


Accepted Solutions
Solution
‎04-07-2016 12:53 PM
Super User
Posts: 5,518

Re: If Then Else statement

[ Edited ]

This is a little more difficult, because you have to base the result on more than one observation.  But then you have to go back and insert the proper result on previous observation(s).

 

Here's one way:

 

proc sort data=have;

by column1 column2;

run;

 

data want;

   length new_column $ 3;

   do until (last.column1);

      set have;

      by column1;

      if column2=1 then new_column='X';

      else if column2=2 then do;

         if new_column=' ' then new_column='Y';

         else if new_column='X' then new_column = 'X,Y';

      end;

   end;

   do until (last.column1);

      set have;

      by column1;

      output;

   end;

run;

 

The top loop examines all records for a value of COLUMN1, and creates NEW_COLUMN.  The bottom loop reads those same records back in, and outputs (including the value for NEW_COLUMN).

View solution in original post


All Replies
Solution
‎04-07-2016 12:53 PM
Super User
Posts: 5,518

Re: If Then Else statement

[ Edited ]

This is a little more difficult, because you have to base the result on more than one observation.  But then you have to go back and insert the proper result on previous observation(s).

 

Here's one way:

 

proc sort data=have;

by column1 column2;

run;

 

data want;

   length new_column $ 3;

   do until (last.column1);

      set have;

      by column1;

      if column2=1 then new_column='X';

      else if column2=2 then do;

         if new_column=' ' then new_column='Y';

         else if new_column='X' then new_column = 'X,Y';

      end;

   end;

   do until (last.column1);

      set have;

      by column1;

      output;

   end;

run;

 

The top loop examines all records for a value of COLUMN1, and creates NEW_COLUMN.  The bottom loop reads those same records back in, and outputs (including the value for NEW_COLUMN).

Super User
Posts: 5,438

Re: If Then Else statement

Since your logic is a bit awkward you need to tell the real data and business requirement logic, otherwise any sugestion might not work in your real task. Data set sort order and business keys...
Data never sleeps
Occasional Contributor
Posts: 16

Re: If Then Else statement

Thank you so much.

 

Best.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 260 views
  • 1 like
  • 3 in conversation