topic Performing a Calculation Based on the Last Observation??? in SAS Procedures

Performing a Calculation Based on the Last Observation???

NeutrinoSAS — Sun, 07 Oct 2012 02:19:08 GMT

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 &in;

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

Re: Performing a Calculation Based on the Last Observation???

Haikuo — Sun, 07 Oct 2012 02:48:02 GMT

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

Re: Performing a Calculation Based on the Last Observation???

art297 — Sun, 07 Oct 2012 02:53:58 GMT

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;