topic Re: Loading All the First Observations Using FIRST or LAG1? in SAS Programming

Loading All the First Observations Using FIRST or LAG1?

Junyong — Mon, 01 Apr 2019 18:11:39 GMT

Suppose an unbalanced panel data set as follows.

data RAW(drop=IT);
call streaminit(1);
do I=1 to 500;
IT=rand("poisson",5);
do T=IT to IT+rand("poisson",5);
X=rand("normal");
output;
end;
end;
run;

There are 500 Is with random but continuous Ts, then one may subset it as follows.

data SUBSET;
set RAW;
by I T;
if first.I;
run;

Or indirectly without FIRST,

data SUBSET;
set RAW;
by I T;
if I^=lag1(I);
run;

Both use IF rather than WHERE to subset. Can I do this with WHERE rather than IF? I tried the following but unsuccessful. Thanks.

data SUBSET;
set RAW(where=(first.I=1));
run;

(failed as FIRST works only after SET and BY)

data SUBSET;
set RAW(where=(I^=lag1(I)));
run;

(failed as LAGs are invalid in WHERE)

proc means data=SUBSET(where=(I^=lag1(I)));
var X;
run;

(failed similarly)

Re: Loading All the First Observations Using FIRST or LAG1?

Tom — Mon, 01 Apr 2019 18:14:49 GMT

Did you try:

where T=IT;

Re: Loading All the First Observations Using FIRST or LAG1?

Junyong — Mon, 01 Apr 2019 18:18:53 GMT

The code is just a working example I made, and I used IT to make the panel data set unbalanced. There is no IT in the real data sets.

Re: Loading All the First Observations Using FIRST or LAG1?

Tom — Mon, 01 Apr 2019 18:29:21 GMT

Then the answer is no. Use the whatever IF statement that works.

Make a view if it bothers you that much.

data first / view=first;
  set have;
  by id;
  if first.id;
run;

Re: Loading All the First Observations Using FIRST or LAG1?

Reeza — Mon, 01 Apr 2019 19:34:46 GMT

You can't use LAG in WHERE because WHERE follows the SQL limitations and doesn't have access to the other rows. IF is a data set construct instead.

Re: Loading All the First Observations Using FIRST or LAG1?

Junyong — Mon, 01 Apr 2019 20:04:15 GMT

It seems I need to explicitly create the FIRST variable before any PROC (or any DATA). Thanks.

Re: Loading All the First Observations Using FIRST or LAG1?

Junyong — Mon, 01 Apr 2019 20:07:19 GMT

So I need to subset (or create the FIRST variable) it in DATA before PROCs or DATAs. Thank you.

Re: Loading All the First Observations Using FIRST or LAG1?

Reeza — Mon, 01 Apr 2019 20:39:36 GMT

@Junyong wrote:
So I need to subset (or create the FIRST variable) it in DATA before PROCs or DATAs. Thank you.

Yes, you may want to just add a flag via a view though as suggested via @Tom

data viewTEmp / view=viewTemp;

set have;
by myGroups;

if first.group then first_flag=1;else first_flag=0;

run;

Then in future queries, just hit the view instead of the main table and you can access the first_flag variable.

proc means data=viewTemp;
where first_flag=1;
run;

Unfortunately this does require that your original data is pre sorted. You could probably write a more complicated SQL view that did this as well, if you need to sort first.