topic first.id not working in SAS Programming

first.id not working

AmirSari — Thu, 11 Mar 2021 21:49:52 GMT

Hi Everyone,

I am having trouble figuring out why first.id in the following code is not working. The problem is the value of var3 from id=1 is copied over to id=2. I am not sure why the first.id statement is ignored in this case. If you run the following code, you can see that the value of var3 id=1 is copied to var3 id=2 for the first two observations which are supposed to be zero.

Th code was provided by @Andreas_Ids

Any help is greatly appreciated!

data gwill;
input id gwill lag_gwill year;
cards;
1 100 . 2000	
1 150 100 2001
1 130 150 2002
1 140 130 2003
1 150 140 2004	
1 120 150 2005
1 110 120 2006
1 100 110 2007
1 140 100 2008
1 160 140 2009
1 180 160 2010
1 110 180 2011
2 200 . 2003
2 200 200 2004
2 170 200 2005
2 150 170 2006
2 150 150 2007
2 140 150 2008
2 170 140 2009

;
run;

data want;
   set gwill;
   by id;
   
   retain start_year var1-var3;
   
   if first.id then do;
      start_year = year;
   end;
   
   select (year);
      when (start_year) var1 = gwill / 10;
      when (start_year + 1) var2 = (gwill - lag_gwill) / 10;
      when (start_year + 2) var3 = (gwill - lag_gwill) / 10;
      when (start_year + 10) var1 = 0;
      when (start_year + 11) var2 = 0;
      when (start_year + 12) var3 = 0;
      otherwise;
   end;
  
   drop start_year;
run;

The results should look like this:

id	gwill	Lag_gwill	year	var1	var2	var3
1	100		2000	10
1	150	100	2001	10	5
1	130	150	2002	10	5	-2
1	140	130	2003	10	5	-2
1	150	140	2004	10	5	-2
1	120	150	2005	10	5	-2
1	110	120	2006	10	5	-2
1	100	110	2007	10	5	-2
1	140	100	2008	10	5	-2
1	160	140	2009	10	5	-2
1	180	160	2010	0	5	-2
1	110	180	2011	0	0	-2
2	200		2003	20
2	200	200	2004	20	0
2	170	200	2005	20	0	-3
2	150	170	2006	20	0	-3
2	150	150	2007	20	0	-3
2	140	150	2008	20	0	-3
2	170	140	2009	20	0	-3

Re: first.id not working

Astounding — Thu, 11 Mar 2021 22:48:30 GMT

The value is held over because that's what you told SAS to do. You said RETAIN those three variables, and that's what RETAIN means.

If you want to re-set var2 and var3 when you begin a new ID, you should say so. In the statement(s) that are based on first.ID, re-set VAR2 and VAR3, not just VAR1.

Re: first.id not working

AmirSari — Thu, 11 Mar 2021 23:03:02 GMT

Thanks for the clarification. How do I reset var1-var3 in order to get my desired output? Would you please provide the code? I am not an expert in SAS.

Re: first.id not working

ballardw — Thu, 11 Mar 2021 23:14:03 GMT

@AmirSari wrote:
Thanks for the clarification. How do I reset var1-var3 in order to get my desired output? Would you please provide the code? I am not an expert in SAS.

   if first.id then do;
      start_year = year;
      call missing(var1, var2,var3);
   end;

Will set var1, var2 and var3 to missing at the start of the ID group.

Re: first.id not working

AmirSari — Thu, 11 Mar 2021 23:21:47 GMT

Thank you!