DATA Step, Macro, Functions and more

How to populate a value for all obs having same by variables in a data step

Reply
Super Contributor
Posts: 272

How to populate a value for all obs having same by variables in a data step

Dear,

 

In my data set the following variable present.

 if there is any change (variable CHG) by id and avisitn, greater than 10, then i need to populate (chgcat1='Max increase >10') for all obs having same id and avisitn

 

id                    avisitn                   chg                 test
1                       2                          8                     sbp
1                        2                          9                    sbp
1                       2                          7                     sbp

1                        3                          12                   dbp

1                       3                           4                      dbp
2                      1                           10                   sbp
2                      1                            11                   sbp
2                     1                              5                     sbp
2                      1                             4                     sbp



output needed


id                          avisitn                      chg                                chgcat1
1                            2                               8
1                            2                               9
1                            2                               7

1                            3                               12                             Max increase >10

1                            3                                 4                             Max increase >10
2                           1                               10                            Max increase >10
2                           1                               11                              Max increase >10
2                           1                                5                                Max increase >10
2                           1                                4                                 Max increase >10

 

The chgcat1 is blank for first 3 obs because none of the obs with id=1 and avisitn=2 have chg greater than 10.

 

I think i can do this in two data steps. is there any way I can in one step as i have to calculate 12 chgcat. Thanks

Valued Guide
Posts: 797

Re: How to populate a value for all obs having same by variables in a data step

Read each group twice.

  In the first group read look for chg>10

 

  In the second group "re-read" output the records:

 

data want;

  do until (last.avistn);

    set have;

    by id avistn;

    if chg>10 then chgcat='Max Increase > 10';

  end;

 

  do until (last.avistn);

    set have;

    by id avistn;

    output;

  end;

run;

 

New Contributor
Posts: 3

Re: How to populate a value for all obs having same by variables in a data step

I'd use the retain statement and have the data sorted. Then you can process in a single datastep as follows:

proc sort data=work.data1;
  by id avisitn descending chg;
run;

data work.data2;
  set work.data1;
  by id avisitn descending chg;
  retain chgcat;
  length chgcat $20;
  if first.avisitn then chgcat = '';
  if chg>10 then chgcat='Max Increase > 10';
run;
Super User
Posts: 9,691

Re: How to populate a value for all obs having same by variables in a data step

You mean MAX > 10 to MAX-MIN > 10 ?


data have;
input id                    avisitn                   chg ;
cards;
1                       2                          8                     sbp
1                        2                          9                    sbp
1                       2                          7                     sbp
1                        3                          22                   dbp
1                       3                           4                      dbp
2                      1                           10                   sbp
2                      1                            11                   sbp
2                     1                              5                     sbp
2                      1                             4                     sbp
;
run;
proc sql;
select *,case when range(chg) gt 10 then 'Max increase >10' else ' ' end as f length=20
 from have
  group by id,avisitn;
quit;


Ask a Question
Discussion stats
  • 3 replies
  • 168 views
  • 3 likes
  • 4 in conversation