Lag() and proc tabulate output

[scify]

So I have the output of a TABULATE constructed as so:

proc tabulate data=super.whytrends out=tabulate format=comma8.1;

   class district screwed whichyear;

   table distrcit, whichyear*screwed*(n rowpctn)/printmiss;

   format district district. screwed screwed.;

   where whichyear=2007 or whichyear=2013;

run;

Screwed is a dummy variable.

I then sorted it based on district and screwed, which left me with a dataset that looks like this:

District      Whichyear      Screwed       n        percent

Dist1         2007               0                   X       Y

Dist1         2013               0                   X       Y

Dist1         2007               1                   X       Y

Dist1         2013               1                   X       Y

etc...

Using http://www.lexjansen.com/nesug/nesug07/cc/cc16.pdf as a reference, I then used lag() to compare the data between different years of the screwed variable.

data tabulate; merge tabulate(drop=_type_ _page_ _table_) otherfile1 otherfile2;

   by circdist;

   seven_pct=lag(pctn_100);

   if whichyear=2007 then seven_pct=.;

   if whichyear=2013 then diff=pctn_100-seven_pct;

run;

This works, but it isn't extensible if I wanted to compare more years. However, when I tried to replace the whichyear= statements with first.screwed and not first.screwed, SAS tossed a "Variable first.screwed is not initialized" error. Is this because screwed isn't part of the by statement? If so, can I put it in the by statement even though it isn't in otherfile1 and otherfile2?

[ballardw · Accepted solution]

To use first. and last. notation the variable has to be on the by statement. Since Merge will get odd results. Merge the data. Then use the Merged data in a separate data step to use the first and last.

data tabulate; merge tabulate(drop=_type_ _page_ _table_) otherfile1 otherfile2;

   by circdist;

run;

data tabulate;

     set tabulate;

     by  cirdist whichyear screwed;

<calculations here>

run;