topic Re: increment by subject using retain in a single datastep in SAS Programming

increment by subject using retain in a single datastep

Jagadishkatam — Sat, 05 Jul 2014 03:27:53 GMT

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 feb

output:

subj month seq

101 jan 1

101 feb 2

Thanks,

Jag

Re: increment by subject using retain in a single datastep

Jagadishkatam — Sat, 05 Jul 2014 03:38:43 GMT

to get the above output, i tried something like below

data have;

input subj month$;

order=_n_;

cards;

101 jan

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

Re: increment by subject using retain in a single datastep

stat_sas — Sat, 05 Jul 2014 03:59:29 GMT

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;

Re: increment by subject using retain in a single datastep

RichardinOz — Sat, 05 Jul 2014 04:13:58 GMT

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

Re: increment by subject using retain in a single datastep

Patrick — Sat, 05 Jul 2014 04:51:52 GMT

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= _:);

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 feb

101 jan

;

run;

Re: increment by subject using retain in a single datastep

Jagadishkatam — Sat, 05 Jul 2014 13:39:00 GMT

Hi all for providing helpful answers.

Thanks,

Jag

Re: increment by subject using retain in a single datastep

sne7189 — Mon, 01 Dec 2014 11:00:13 GMT

Im having a query

dataset test consists of a numeric variable X

----------

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

Re: increment by subject using retain in a single datastep

sne7189 — Mon, 01 Dec 2014 11:00:48 GMT

Im having a query

dataset test consists of a numeric variable X

----------

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

Re: increment by subject using retain in a single datastep

Jagadishkatam — Mon, 01 Dec 2014 13:32:03 GMT

Please try

data have;

input x;

ord=_n_;

cards;

;

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