BookmarkSubscribeRSS Feed
ds5000
Calcite | Level 5

Hello,

 

Suppose I have this dataset:

 

Name   Value

A              --

A              22

A              --

A              --

A              35

A              --

A              --

B              --

B              3

B              7

B              --

B              --

B              90

B              --

 

What I want to accomplish is that for every "Name" I want to subset the observations between first time the value was observed and last time the value was observed.

Desired dataset:

Name       Value

A               22

A               -- 

A               --

A               35

B               3

B               7

B               --

B               -- 

B               90

 

Please help. Thanks.

2 REPLIES 2
TomKari
Onyx | Level 15

I think this is going in the right direction.

 

Tom

 

data have;

length NAME $8;

input NAME VALUE;

SeqNo = _N_;

cards;

A .

A 22

A .

A .

A 35

A .

A .

B .

B 3

B 7

B .

B .

B 90

B .

run;

data inter1;

set have;

by NAME;

retain GoodSwitch;

if first.NAME then

GoodSwitch = 0;

if ^missing(VALUE) then

GoodSwitch = 1;

run;

proc sort data=inter1 out=inter2;

by descending SeqNo;

run;

data inter3;

set inter2;

by NAME notsorted;

retain FirstFound;

if first.NAME then

FirstFound = 0;

if ^missing(VALUE) then

FirstFound = 1;

if FirstFound = 0 then

GoodSwitch = 0;

run;

proc sort data=inter3 out=inter4;

by SeqNo;

run;

data want;

set inter4;

if GoodSwitch then

output;

run;

PGStats
Opal | Level 21

The double do until is ideal for this

 

data have;
input Name $  Value :??best.;
datalines;
A              --
A              22
A              --
A              --
A              35
A              --
A              --
B              --
B              3
B              7
B              --
B              --
B              90
B              --
C              --
;

data want;
do i=1 by 1 until(last.name);
    set have; by name;
    if not missing(value) then do;
        if missing(first) then first = i;
        last = i;
        end;
    end;
do i=1 by 1 until(last.name);
    set have; by name;
    if i >= first then
        if i <= last then output;
    end;
drop first last i;
run;

proc print; run;

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 950 views
  • 0 likes
  • 3 in conversation