Lag() and proc tabulate output

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Lag() and proc tabulate output

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?


Accepted Solutions
Solution
‎09-16-2014 03:15 PM
Grand Advisor
Posts: 10,210

Re: Lag() and proc tabulate output

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;

View solution in original post


All Replies
Solution
‎09-16-2014 03:15 PM
Grand Advisor
Posts: 10,210

Re: Lag() and proc tabulate output

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 143 views
  • 0 likes
  • 2 in conversation