## FIRST. LAST. VARIABLES

Solved
Super Contributor
Posts: 276

# FIRST. LAST. VARIABLES

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....

Accepted Solutions
Solution
‎01-23-2012 12:48 PM
PROC Star
Posts: 8,163

## FIRST. LAST. VARIABLES

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;

All Replies
Super Contributor
Posts: 1,636

## Re: FIRST. LAST. VARIABLES

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

PROC Star
Posts: 8,163

## FIRST. LAST. VARIABLES

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;

Super Contributor
Posts: 276

## FIRST. LAST. VARIABLES

its working...

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

what should i do????

Solution
‎01-23-2012 12:48 PM
PROC Star
Posts: 8,163

## FIRST. LAST. VARIABLES

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;

Valued Guide
Posts: 765

## Re: FIRST. LAST. VARIABLES

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;

Super Contributor
Posts: 1,636

## FIRST. LAST. VARIABLES

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

Super User
Posts: 8,073

## FIRST. LAST. VARIABLES

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;

Super Contributor
Posts: 276

## FIRST. LAST. VARIABLES

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....

Super User
Posts: 8,073

## FIRST. LAST. VARIABLES

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;

Super Contributor
Posts: 1,636

## FIRST. LAST. VARIABLES

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;

🔒 This topic is solved and locked.