Performing a Calculation Based on the Last Observation???

[NeutrinoSAS]

Hi!  I need to do the following... go through a data sheet and if a certain condition occurs increase the count of a variable

I need to do this for four variables - (given four different conditions)

Then I need to plug in the final value of the four different count variables into a macro.  My issue is that SAS stores a count variable as 1,2,3,4,5,6 and not just the end total 6.

So given the following code

data work

set testxls.'Sheet1$'n ;

if X ~= . and Y = 'QWE' then L1 + 1;

if X = . and Y = 'QWE' then L2 + 1* ;

if X ~= . and Y ~= 'ABC' then L3 + 1;

if X = . and Y ~= 'ABC' then L4 + 1;

run;

So in this instance L1. eg, is 1,1,1,1,1,1,1,2,3,3,4,5,6,7

Now i have a macro (which i cant change) with the format

macro XCV(in,out)

data &out; set ∈

if (L1 = 0 or L2 = 0 or L3 =0 or L4=0) then do;

blah blah blah

end;

else do;

blah blah blah

end;

????????

How do I isolate the final values for L1 - L4?  If I do %XYZ(work,out).  The out output file contains functions for ever value of L1-L4 i.e for L1 1,1,1,1,1,1,1,2,3,3,4,5,6,7

[Haikuo]

Hi,

If I understand you correctly, you need one more data step pass to fix L1-L4 on their maximum values, or switch to proc SQL:

data work;

do until (last);

set testxls.'Sheet1$'n end=last;

if X ~= . and Y = 'QWE' then L1 + 1;

if X = . and Y = 'QWE' then L2 + 1* ;

if X ~= . and Y ~= 'ABC' then L3 + 1;

if X = . and Y ~= 'ABC' then L4 + 1;

end;

do until (last);

set testxls.'Sheet1$'n end=last;

output;

end;

run;

/*Or Modify your code to proc sql*/

proc sql;

  create table work as

    select *,

           sum (X ~= . and Y = 'QWE') as L1,

           sum (X = . and Y = 'QWE') as L2,

           sum (X ~= . and Y ~= 'ABC') as L3,

           sum (X = . and Y ~= 'ABC' ) as L4

        from testxls.'Sheet1$'n    ;

quit;

Then use this 'work' in your downstream Macro.

Haikuo

[art297]

Why not just modify your code to something like? (note: I can't test this tonight, but I would assume that the end option works with spreadsheets):

data work

set testxls.'Sheet1$'n end=eof;

   if X ~= . and Y = 'QWE' then L1 + 1;

   if X = . and Y = 'QWE' then L2 + 1* ;

   if X ~= . and Y ~= 'ABC' then L3 + 1;

   if X = . and Y ~= 'ABC' then L4 + 1;

  if eof then output;

run;