BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jagadishkatam
Amethyst | Level 16

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
1 ACCEPTED SOLUTION

Accepted Solutions
RichardinOz
Quartz | Level 8

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

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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
stat_sas
Ammonite | Level 13

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;

RichardinOz
Quartz | Level 8

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

sne7189
Calcite | Level 5

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

Patrick
Opal | Level 21

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    jan

101    jan

101    feb

101    feb

101    feb

101    jan

;

run;

Jagadishkatam
Amethyst | Level 16

Hi all for providing helpful answers.

Thanks,

Jag

Thanks,
Jag
sne7189
Calcite | Level 5

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

Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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