Showing posts with label base. Show all posts
Showing posts with label base. Show all posts

Wednesday, May 11, 2011

SUM within subgroup

/* Within the subgroup of ID, if seq is the same, then calculate the sum of unit for the same seq;*/

/* Else, if seq is different then keep the value of unit */

options formdlim='-';

data test;

input ID seq unit;

cards;

100 1 10

100 2 10

101 1 10

101 1 20

103 1 10

103 2 10

104 1 10

104 1 20

;

run;

/* First is to use DATA STEP */

proc sort data=test;

by id seq;

run;

data want (keep=id seq sumunit);

set test;

by id seq;

if first.id or first.seq then sumunit=unit;

else sumunit+unit;

if last.id or last.seq then output;

run;

proc print data=want;

run;

/* Second is PROC SQL with GROUP BY */

proc sql;

select id, seq, sum(unit) as s

from test

group by id, seq

order by id, seq;

run;

quit;

Wednesday, January 26, 2011

If the difference of two consecutive number is less then 7, then assign it as missing

options formdlim=' ';

/***************************************************************************
*  If the difference of two consecutive number is less then 7, then assign it as missing;
*  First, the target numbers will be set as 0; If set as missing ,there is problem;
*  Next, set the zeros to be missing.
***************************************************************************/

data temp;
input d1-d6;
cards;
1 2 7 23 100 1000
2 3 33 54 56 1000
3 . 4 6 44 100
;
run;

data temp1;
   set temp;
array ss(1:6) d1-d6;
array s(1:6) d1-d6;
do i=2 to 6;
  if s[i]-s[i-1]<=7 then do;
     ss[i]=0;  /* Here if set as missing, there is problems */
        ss[i-1]=0;
        end;
end;
run;

data want;
  set temp1;
  array s d1-d6;
  do i=1 to 6;
    if s[i]=0 then s[i]=.;
   end;
run;


proc print data=want;
run;


Saturday, January 22, 2011

Get the different chars appearance frequency

options formdlim='-';

************* Another Problem form MITBBS *****************;
* The question like this: have data x like below consists of 1 and 2 only *;
* Want to count the frequency of 1's and 2's appear consecutively *;
* More detailed, how many times 1 appear once consecutively *;
* More detailed, how many times 1 appear twice consecutively *;
* And so on *;
* Until how many times 2 appear ** times consecutively ? *;
* That is, the result should be: *;
/**************************************************************
                         x   counter      freq
                      ----------------------------
                         1         1         3
                         1         4         1
                         2         2         2
                         2         3         1
                         2         5         1
**************************************************************/


data test;
  input x;
  cards;
  1
  2
  2
  1
  1
  1
  1
  2
  2
  2
  2
  2
  1
  2
  2
  1
  2
  2
  2
  ;
run;


data new;
   set test;
   by x notsorted;
   retain counter;
   if first.x then counter=0;
      counter+1;
   if last.x then output;
run;

proc print data=new;
run;

proc sql;
   select x, counter, n(counter) as freq
   from new
   group by x, counter
   order by x, counter
   ;
quit;


***  If don't use proc sql, we can use these data steps: **;
data new;
   set test;
   by x notsorted;
   retain counter;
   if first.x then counter=0;
      counter+1;
   if last.x then output;
run;

proc print data=new;
run;

proc sort data=new;
  by x  counter;
run;

data want;
  set new;
  by x  counter;
  retain freq;
  if first.x  or first.counter then  freq=1;
    else freq=freq+1;
  if last.x or last.counter then output;
run;

proc print data=want;
run;

Thursday, January 20, 2011

How to report the position of a given character inside a string

/*******************************************************************
  SAS: How to report the position of a given character inside a string
  e.g.: Here have a tring abc_de_fg_ac_ff, we want to get the position
  of '_' in the string. That is, we want to get 4, 7, 10, 13

  In the following two methods are given, the first one using macro. It
  works like this: First, count how many '_' are there in the string.
  Then scan data until find the first '_', report its position. Then from
  this position, scan the rest string for the second one. And so on.

  The second method using data step. Scan the string, when meets '_',
  then report its position. This is easier to understand and code.
*********************************************************************/

******************  Method One   ********************************;

%macro ss(str,dlm);
  %let stt=1;
  %let pos=0;
  %do i=1 %to %sysfunc(count(&str,%str(&dlm))) %by 1;
     %let pos=%eval(%index(%substr(&str,&stt),&dlm)+&stt-1);
        %let stt=%eval(&pos+1);
        %put &pos;
   %end;
%mend ss;

%ss(abc_de_fg_ac_ff,_)


*****************  Method Two **********************************;

data s1;
  str='abc_de_fg_ac_ff';
run;

data s2;
  set s1;
  do i = 1 to length(str);
    if substr(str,i,1)="_" then put  i;
  end;
run;