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
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.