increment by subject using retain in a single datastep

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,129
Accepted Solution

increment by subject using retain in a single datastep

Dear All,

Could you please help me to get the output as below in a single datastep. For the same i used two datasteps one to get the increment and other by merge statement. However i would like to know if there is another way to get the same result in a single datastep.

input:

subj  month

101    jan

101    jan

101    jan

101    feb

101    feb

101    feb

output:

subj month seq

101    jan     1

101    jan     1

101    jan     1

101    feb     2

101    feb     2

101    feb     2

Thanks,

Jag

Thanks,
Jag

Accepted Solutions
Solution
‎07-05-2014 12:13 AM
Super Contributor
Posts: 644

Re: increment by subject using retain in a single datastep

Jag

Your main problem is you are entering month as a name rather than as a date, so the months will not sort properly.  The solution above works for a single subj provided all the months are in the same year and the data is in intuitive order. To generalise to more than one subject you need to reset the counter for each subj.

data want;

    set have;

    by subj month notsorted;

     retain seq ;

     if first.subj then seq=0 ;

    if first.month then seq+1;

run;


Richard

View solution in original post


All Replies
Trusted Advisor
Posts: 1,129

Re: increment by subject using retain in a single datastep

to get the above output, i tried something like below

data have;   

    input subj month$;

    order=_n_;

cards;

101    jan

101    jan

101    jan

101    feb

101    feb

101    feb

;

proc sort data = have out=have2 nodupkey;

by subj descending month;

run;

data want;

    set have2 ;

    by subj descending  month;

    retain seq;

    if first.subj then seq=1;

    else seq+1;

    if last.month;

run;

proc sort data = have;

by subj month;

run;

proc sort data = want;

by subj month;

run;

data want2;

   merge have(in=a) want(in=b);

   by subj month;

run;

Could you please make it simple.

Thanks,

Jag

Thanks,
Jag
Trusted Advisor
Posts: 1,204

Re: increment by subject using retain in a single datastep

data have;
    input subj month$;
cards;
101    jan
101    jan
101    jan
101    feb
101    feb
101    feb
;

data want;
    set have;
    by subj month notsorted;
if first.month then seq+1;
run;

Solution
‎07-05-2014 12:13 AM
Super Contributor
Posts: 644

Re: increment by subject using retain in a single datastep

Jag

Your main problem is you are entering month as a name rather than as a date, so the months will not sort properly.  The solution above works for a single subj provided all the months are in the same year and the data is in intuitive order. To generalise to more than one subject you need to reset the counter for each subj.

data want;

    set have;

    by subj month notsorted;

     retain seq ;

     if first.subj then seq=0 ;

    if first.month then seq+1;

run;


Richard

Occasional Contributor
Posts: 12

Re: increment by subject using retain in a single datastep

Im having a query

dataset test consists of a numeric variable X

X

----------

1

1

1

2

1

1

2

2

3

3

4

when ever sas reads the first value of X a new variable

Y should contain the same value.

Then again if the 2nd observation is same as first observation value of Y should increment accordingly

If a value is not as not the previous one

eg if X=2 is encountered then Y value should again start from 1 and increment by 1 accordingly.

the process should go on till end of file.

Output table should look like

-----------------------------------

X   Y

-----------------------------------

1 1

1 2

1 3

2 1

1 4

1 5

2 2

2 3

3 1

3 2

4 1

kindly help me to write a sas code in this

Respected Advisor
Posts: 3,892

Re: increment by subject using retain in a single datastep

I assume you want such a sequence number to be able to order your data per ID and Month. Ideally your "month" variable would be numeric containing a SAS date value.


Below an approach which allows you to create a month number based on the month name.

data work.fmt_source (drop= _Smiley Happy;

  fmtname='Mon_Seq';

  type='I';

  _start_dt='01jan1960'd;

  do label=1 to 12;

    start=put(intnx('month',_start_dt,label-1,'s'),monname3.);

    output;

  end;

run;

proc format cntlin=work.fmt_source;

run;

data sample;

  input subj month $;

  seq=input(propcase(month),Mon_Seq.);

  cards;

101    jan

101    jan

101    jan

101    feb

101    feb

101    feb

101    jan

;

run;

Trusted Advisor
Posts: 1,129

Re: increment by subject using retain in a single datastep

Hi all for providing helpful answers.

Thanks,

Jag

Thanks,
Jag
Occasional Contributor
Posts: 12

Re: increment by subject using retain in a single datastep

Im having a query

dataset test consists of a numeric variable X

X

----------

1

1

1

2

1

1

2

2

3

3

4

when ever sas reads the first value of X a new variable

Y should contain the same value.

Then again if the 2nd observation is same as first observation value of Y should increment accordingly

If a value is not as not the previous one

eg if X=2 is encountered then Y value should again start from 1 and increment by 1 accordingly.

the process should go on till end of file.

Output table should look like

-----------------------------------

X   Y

-----------------------------------

1 1

1 2

1 3

2 1

1 4

1 5

2 2

2 3

3 1

3 2

4 1

kindly help me to write a sas code in this

Trusted Advisor
Posts: 1,129

Re: increment by subject using retain in a single datastep

Please try

data have;

input x;

ord=_n_;

cards;

1

1

1

2

1

1

2

2

3

3

4

;

run;

proc sort data=have;

by x;

run;

data want;

set have;

by x;

if first.x then y=1;

else y+1;

run;

proc sort data=want;

by ord;

run;

Thanks,

Jag

Thanks,
Jag
☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 888 views
  • 6 likes
  • 5 in conversation