DATA Step, Macro, Functions and more

case when

Reply
Contributor
Posts: 28

case when

 logic is something like

case when yyyymm=201512 and id_flg >0 then 1

case when yyyymm=201511and id_flg >0 then 2

case when yyyymm=201510 and id_flg >0 then 3

case when yyyymm=201509 and id_flg >0 then 4

else . end as var_m1_m4.

 

In case if i have id_flg value across each month i get var_m1_m4 as 4  .

Output required: if id_flg  gt 0 for 201512 then var_m1_m4 =1 and system skip further processing.

in case if  1st condition doesn't satisfy than it should go and look for next step.

 

Thanks in advance.

Super User
Posts: 19,869

Re: case when

Posted in reply to LearnByMistk

Please post some sample data and example output. 

Contributor
Posts: 28

Re: case when

subscriber_num   yyyymm id_flg

101     201512    1

102     201512     .

102     201511     1

103   201512      .

103   201511     .

103   201510     1

 

 

ouptut

subscr     var_m1_m4

101          1

102           2

103            3 

 

 

consider values for month 201512 will have var_m1_m4 as 1 values in 201511 will be 2 and so on 

Super User
Posts: 7,859

Re: case when

Posted in reply to LearnByMistk
data have;
input yyyymm $6. id_flg;
cards;
2015121
2015111
2015101
2015091
2015120
2015110
2015100
2015090
2015081
2015080
2016011
2016010
;
run;

data want;
set have;
if id_flg > 0
then do;
  var_m1_m4 = intck('month',input(yyyymm!!'01',yymmdd8.),'30jan2016'd);
  if var_m1_m4 > 4 or var_m1_m4 < 1 then var_m1_m4 = .;
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: case when

Posted in reply to KurtBremser
nope that's not correct sir, kindly look at the example
Super User
Posts: 7,859

Re: case when

[ Edited ]
Posted in reply to LearnByMistk

Slightly updated code (after getting some example data):

data have;
length yyyymm $6;
input subscriber_num yyyymm id_flg;
cards;
101 201512 1
102 201512 .
102 201511 1
103 201512 .
103 201511 .
103 201510 1
;
run;

data want (keep=subscriber_num var_m1_m4);
set have;
if id_flg > 0
then do;
  var_m1_m4 = intck('month',input(yyyymm!!'01',yymmdd8.),'30jan2016'd);
  if 1 <= var_m1_m4 <= 4 then output;
end;
run;

renders this:

101	1
102	2
103	3

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: case when

Posted in reply to KurtBremser

Hey thanks for reverting ..my apology to reply late.

We can get the same results using case when but i dnt know how to exit code once my condition fulfill .

as Have :

data have;
length yyyymm $6;
input subscriber_num yyyymm id_flg;
cards;
101 201512 1
101 201511 23
101 201510 223
102 201512 .
102 201511 1
102 201501 54
103 201512 .
103 201511 .
103 201510 1
;
run;

 

 

Want

subscriber var_m1_m4

101                     1

102                     2

103                     3

 

what i dont need is

subscriber var_m1_m4

101                     1

 101                     2

101                     3


KurtBremser wrote:

Slightly updated code (after getting some example data):

data have;
length yyyymm $6;
input subscriber_num yyyymm id_flg;
cards;
101 201512 1
102 201512 .
102 201511 1
103 201512 .
103 201511 .
103 201510 1
;
run;

data want (keep=subscriber_num var_m1_m4);
set have;
if id_flg > 0
then do;
  var_m1_m4 = intck('month',input(yyyymm!!'01',yymmdd8.),'30jan2016'd);
  if 1 <= var_m1_m4 <= 4 then output;
end;
run;

renders this:

101	1
102	2
103	3

 


 

102                     2

103                     3

 

Super User
Posts: 7,859

Re: case when

Posted in reply to LearnByMistk

Then we need to add an additional flag for the processing of each subscriber_num:

data have;
length yyyymm $6;
input subscriber_num yyyymm id_flg;
cards;
101 201512 1
101 201511 23
101 201510 223
102 201512 .
102 201511 1
102 201501 54
103 201512 .
103 201511 .
103 201510 1
;
run;

data want (keep=subscriber_num var_m1_m4);
set have;
by subscriber_num;
retain flag;
if first.subscriber_num then flag = 1;
if id_flg > 0 and flag
then do;
  var_m1_m4 = intck('month',input(yyyymm!!'01',yymmdd8.),'30jan2016'd);
  if 1 <= var_m1_m4 <= 4
  then do;
    output;
    flag = 0;
  end;
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 330 views
  • 0 likes
  • 3 in conversation