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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.