FIRST. LAST. VARIABLES

[kuridisanjeev]

Hi,,

data test;

input zone $ state $ amount;

cards;

ZONE1 AP 121

ZONE1 KA 125

ZONE1 TM 129

ZONE1 UP 150

ZONE2 QW 130

ZONE2 VF 126

ZONE2 CV 129

ZONE2 LK 135

ZONE3 PL 123

ZONE3 NJ 165

ZONE3 OI 123

ZONE4 QA 176

ZONE4 UY 143

;

RUN;

PROC PRINT;

RUN;

From above data i want to print every second state in each zone by using first. and last . variable concept....

Thanks in Advance.....

[Linlin]

data test;

input zone $ state $ amount;

cards;

ZONE1 AP 121

ZONE1 KA 125

ZONE1 TM 129

ZONE1 UP 150

ZONE2 QW 130

ZONE2 VF 126

ZONE2 CV 129

ZONE2 LK 135

ZONE3 PL 123

ZONE3 NJ 165

ZONE3 OI 123

ZONE4 QA 176

ZONE4 UY 143

;

RUN;

data want;

  set test;

  by zone;

  if first.zone then delete;

data want;

  set want;

  by zone;

  if first.zone;

run;

PROC PRINT;

RUN;

                                 Obs    zone     state    amount

                                  1     ZONE1     KA        125

                                  2     ZONE2     VF        126

                                  3     ZONE3     NJ        165

                                  4     ZONE4     UY        143

Linlin

[art297]

I'm not sure if this is what you are looking for, but it gets each 2nd state using first.variable:

data test;

  input zone $ state $ amount;

  cards;

ZONE1 AP 121

ZONE1 KA 125

ZONE1 TM 129

ZONE1 UP 150

ZONE2 QW 130

ZONE2 VF 126

ZONE2 CV 129

ZONE2 LK 135

ZONE3 PL 123

ZONE3 NJ 165

ZONE3 OI 123

ZONE4 QA 176

ZONE4 UY 143

;

RUN;

data want (drop=x);

  set test;

  by zone;

  x=ifn(first.zone,0,lag(first.zone));

  if x;

run;

PROC PRINT;

RUN;

[kuridisanjeev]

its working...

but if i want 3rd state in each zone????

what should i do????

[art297 · Accepted solution]

Same way, but use lag2.  e.g.:

data want (drop=x);

  set test;

  by zone;

  x=ifn(first.zone,0,lag2(first.zone));

  if x;

run;

[MikeZdeb]

hi ... here's another idea

just specify the number you want in the IF statement (1, 2 , 3, whatever)

data third (drop=count);

set test;

by zone;

count + (-first.zone*count) + 1;

if count eq 3;

run;

[Linlin]

data test;

input zone $ state $ amount;

cards;

ZONE1 AP 121

ZONE1 KA 125

ZONE1 TM 129

ZONE1 UP 150

ZONE2 QW 130

ZONE2 VF 126

ZONE2 CV 129

ZONE2 LK 135

ZONE3 PL 123

ZONE3 NJ 165

ZONE3 OI 123

ZONE4 QA 176

ZONE4 UY 143

;

RUN;

data want;

  set test;

  by zone;

   if first.zone then count=1;else

   count+1;

if count=3;

proc print;run;

                             Obs    zone     state    amount    count

                              1     ZONE1     TM        129       3

                              2     ZONE2     CV        129       3

                              3     ZONE3     OI        123       3

[Tom]

You could increment a counter at the first record for a state. Reset the counter to 1 at the start of a zone.

This will make it easy to select the 2nd, 3rd, ... state.

data want ;

   set have;

   by zone state;

   if first.zone then order=1;

   else order+first.state;

   if order =2 ;

run;

[kuridisanjeev]

Thank you....

one more question...

This is the file i impored from excel sheet..

                                        east        100

                                                      150

                                                      165

                                        west        230

                                                      430

                                                      230

i want to make this data like this...

                                        east        100

                                        east        150

                                        east        165

                                        west        230

                                        west        430

                                       west         230

slove this....

[Tom]

This is a common task.  Solution is to make a new variable that will retain the last non-missing value.

Lets assume your variable's name is REGION.

Something like:

data want ;

   set have;

   retain _region;

   drop _region;

   if region = ' ' then region=_region;

   else _region = region;

run;

[Linlin]

data have;

input region $ 1-4 amount 5-7;

cards;

east 100

     150

     165

west 230

     430

     230

;

data want (drop=hold);

   retain hold ;

   set have;

   if region ne ' ' then hold=region;

   if region eq ' ' then region=hold;

run;

proc print;run;