Adding numbers based on id

Accepted Solution Solved
Reply
Regular Contributor
Posts: 188
Accepted Solution

Adding numbers based on id

I have a dataset like this:

ID  VAR1

1    3

1    4

1    6

2    5

2    5

I want to add a variable like this:

ID  VAR1 Time

1    3        1

1    4        2

1    6        3

2    5        1

2    5        2

How to do that?


Accepted Solutions
Solution
‎07-02-2014 05:07 AM
Super User
Super User
Posts: 7,401

Re: Adding numbers based on id

Hi,

With your first data, assuming its sorted:

data want;

     set have;

     retain time;

     by id;

     if first.id then time=1;

     else time=time+1;

run;

View solution in original post


All Replies
Solution
‎07-02-2014 05:07 AM
Super User
Super User
Posts: 7,401

Re: Adding numbers based on id

Hi,

With your first data, assuming its sorted:

data want;

     set have;

     retain time;

     by id;

     if first.id then time=1;

     else time=time+1;

run;

Regular Contributor
Posts: 188

Re: Adding numbers based on id

i want the time to be in year format like 1994 and else increase by 1. i tried this unsuccessfully:

format year year.;

if first.id then year=1994;else year=year+1;

Super User
Super User
Posts: 7,401

Re: Adding numbers based on id

Hi,

This works for me:

data have;
  id=1;var1=3;output;
  id=1;var1=4;output;
  id=1;var1=6;output;
  id=2;var1=5;output;
  id=2;var1=5;output;
run;

data want;
  set have;
  retain time;
  by id;
  if first.id then time=1994;
  else time=time+1;
run;

Regular Contributor
Posts: 188

Re: Adding numbers based on id

but that will be a number and not date format. I want the sas to treat 1994 and other added numbers as Year, because I will be using it as date in calculation.

Super User
Super User
Posts: 7,401

Re: Adding numbers based on id

Ok:

data want;

  set have;

  format time year4.;

  retain time;

  by id;

  if first.id then time='01JAN1994'd;

  else time=intnx('year', time, 1, 'same');

run;

Regular Contributor
Posts: 188

Re: Adding numbers based on id

I have another variable which i am reading as year:

format year1 year4.;

year1=year(t_date);

if year1=. then year1=0;

but its creating year1 as 1960 for year1=. and some other values like 1965 for year 2011. its important to convert to same format before comparing like, without format year4. its taking format of best12, we can't compare it with year4 format created earlier right?

Super User
Super User
Posts: 7,401

Re: Adding numbers based on id

A date variable, be it date9, yymmdd10, or year4. are all the same structure, that being a number from a certain date.  You cannot directly put a number into the date format, but you need to state it is a date, so behind the scenes SAS converts it to its internal number.  You can see this by formatting a date variable as best.  It will be a big number.  So as per my example, I set the initial year using:

time='01JAN1994'd;  This is a date literal specified by the d.  To increment this by one year, you need to add 365 day * number of years (roughly), or rather use date increment functions like intnx.  Comparing a number, eg 1994 versus a date 145234 (01JAN2014 just an example) will not work.  You would need to do: if mdy('01','01',put(year,4.)) = your_date.

Super Contributor
Posts: 644

Re: Adding numbers based on id

Here is what is happening:

format year1 year4.;

     So far, so good.  year1 is defined as a SAS date using the year format.  A valid SAS date will be displayed showing only the year.  The      underlying data information is unchanged.

year1=year(t_date);

     Hmmm.  The year function is converting a valid SAS date into a year number.  Now the year format is no longer appropriate for      displaying year1 as a year.  When the format is applied to a year value of 2014 it will treat the 2014 as the number of days from SAS      day 0, which happens to be 1JAN1960; so it reports 1965:

43         data _null_ ;

44         format year1 year. ;

45         year1 = 2014 ;

46         put year1=;

47         run ;

year1=1965

Instead, leave year1 unformatted when it will display correctly as a year number;

if year1=. then year1=0;

Now if year1 is unformatted, this code should work.

However, if year1 is formatted as year. then it will interpret the value as 0 days from 1Jan1960, and report 1960 as you have seen.

In SAS you cannot provide a date that will format to 0 using the year. format.  You might think that the date 1jan0000 would work, but SAS declares all dates prior to the adoption of the Gregorian calendar as invalid.  This at least is better than Excel which stutters at dates before 1jan1900, or in some cases 1jan1904.

Richard

Regular Contributor
Posts: 188

Re: Adding numbers based on id

Why is this giving me missing values:

data pre_final;

set pre_final1;by compid;

retain start end;

if first.compid then do;

start=0;

end=1;

end;

else do;

start=start+1;

end=end+1;

end;

run;

Super User
Super User
Posts: 7,401

Re: Adding numbers based on id

Hi,

Can you supply some test data this is happening on?

Regular Contributor
Posts: 188

Re: Adding numbers based on id

Its same as the ID in the original discussion, but instead of time, i am trying to code start and end variables.

Regular Contributor
Posts: 188

Re: Adding numbers based on id

Seems to be working fine now. Not sure what was the cause

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 375 views
  • 4 likes
  • 3 in conversation