Help using Base SAS procedures

To capture the value of First and Last variable

Reply
Regular Contributor
Posts: 229

To capture the value of First and Last variable

Hi i want to capture the value of first.sasid , first.cul ,last.sasid,lastcul values into new variables how can i do it

if the value of sasid for first.sasid is 3 then i want to move this value to new variable f_s=3 and

if last.sasid=2 then l_s=2

if first.cul =14  then i want to move this value to new variable f_c=14

if last.cul =28  then i want to move this value to new variable l_c=28

data a;

input sasid cul dt ddmmyy10.;

format dt ddmmyy10.;

cards;

1 23 12/10/10

1 23 15/10/10

1 23 16/10/10

2 44 15/10/10

2 34 12/10/10

2 44 16/10/10

3 14 12/10/10

3 24 15/10/10

3 56 16/10/10

3 18 10/10/10

run;

PROC Star
Posts: 7,467

To capture the value of First and Last variable

I'm not sure that I correctly understand what you want.  Hopefully, the following at least comes close:

data want (keep=sasid f_c l_c);

  set a;

  by sasid;

  retain f_c;

  if first.sasid then f_c=cul;

  if last.sasid then do;

    l_c=cul;

    output;

  end;

run;

SAS Super FREQ
Posts: 8,862

Re: To capture the value of First and Last variable

Hi:

  When you use FIRST. and LAST. variables with BY group processing, the values of the automatic FIRST.byvar and LAST.byvar are either 0 or 1 (either an observation is the first of the a BY group or it's not). So, your IF statements would not work:

if last.sasid=2

if first.cul =14

if last.cul =28

                  

  While Art has pointed out some code for you to try, it is important to understand the fundamental nature of how FIRST.byvar and LAST.byvar work. For example, the following program

proc sort data=a;
  by sasid cul dt;
run;
      
data capture_val;
  set a;
  by sasid cul;
  hold_fs = first.sasid;
  hold_ls = last.sasid;
  hold_fc = first.cul;
  hold_lc = last.cul;
   
  ** test condition;
  if last.sasid = 1 and sasid = 2 then message = 'last.sasid = 1 and sasid=2';
run;
     
options nocenter nodate nonumber;
proc print data=capture_val;
  title 'Values of FIRST. and LAST. variables are 0 or 1';
run;

produces this output from the PROC PRINT. You can see that the "hold" values for FIRST.SASID, LAST.SASID, FIRST.CUL and LAST.CUL are only 0 or 1. Since the automatic variables are not kept in the output dataset, I created "hold" versions of each of the FIRST. and LAST. automatic variables. In addition, for the test, IF LAST.SASID=1 and SASID = 2, you can see that only 1 observation in the input data met that criterion and had the MESSAGE variable assigned a value as a result of the IF statement being true.

cynthia

The LISTING output from the above program (using your DATA A example) is shown below. As you can see from the color coding that I added to the output, there is only 1 observation that has hold_fs=1 (FIRST.SASID value) for each SASID and only 1 observation that has hold_ls=1 (LAST.SASID value) for each SASID. In your data, you have multiple observations per SASID -- so, only observation #6 meets the criteria if last.sasid=1 and sasid=2, as the created variable MESSAGE shows:

Values of FIRST. and LAST. variables are 0 or 1

             

Obs  sasid  cul          dt  hold_fs  hold_ls  hold_fc  hold_lc           message

  1    1     23  12/10/2010     1        0        1        0
  2    1     23  15/10/2010     0        0        0        0
  3    1     23  16/10/2010     0        1        0        1
  4    2     34  12/10/2010     1        0        1        1
  5    2     44  15/10/2010     0        0        1        0
  6    2     44  16/10/2010     0        1        0        1     last.sasid = 1 and sasid=2
  7    3     14  12/10/2010     1        0        1        1
  8    3     18  10/10/2010     0        0        1        1
  9    3     24  15/10/2010     0        0        1        1
10    3     56  16/10/2010     0        1        1        1

Trusted Advisor
Posts: 1,301

To capture the value of First and Last variable

Hi,

I understood your intended result slightly differently as tring to 'event' by your dt var.  Your question is not really specific to your intended problem/solution.

data a;

input sasid cul dt ddmmyy10.;

format dt ddmmyy10.;

cards;

1 23 12/10/10

1 23 15/10/10

1 23 16/10/10

2 44 16/10/10

2 34 12/10/10

2 44 16/10/10

3 14 12/10/10

3 24 15/10/10

3 56 16/10/10

3 18 10/10/10

;

run;

proc sql;

create table b as

select dt, min(sasid) as f_s, max(sasid) as l_s, min(cul) as f_c, max(cul) as l_c, count(1) as cnt

   from a

  group by dt

  order by dt;

quit;

proc print data=b; run;

obs     dt               f_s     l_s     f_c     l_c     cnt

1     10/10/2010     3          3      18      18       1

2     12/19/2010     1          3      14      34       3

3     15/10/2010     1          3      23      24       2

4     16/10/2010     1          3      23      56       4

Ask a Question
Discussion stats
  • 3 replies
  • 187 views
  • 0 likes
  • 4 in conversation