CODEWITHSUNDEEP
sassas-macro
CrudeOperator
CrudeOperator

Reputation: 111

SAS: How to get value from table based on value from another variable, and assign to macro variable?

I have a dataset which relates to status changes. From that table I've then created a PROC FREQ table which counts the number of times a particular status appears and I've outputted that to a work file. The below is what the outputted PROC FREQ table looks like:

Status COUNT PERCENT
Successful 500 0.8
Unsuccessful 100 0.16
Unknown 25 0.04

Looking at the outputted PROC FREQ table, where the status is equal to "Successful", I want to take the corresponding PERCENT value and assign it to a macro variable so I can refer to that value at a later point.

I read a little about the SYMGET/SYMPUT procedures though I don't know enough to know if they're appropriate.

I intend to use that macro variable value within a macro function, if the macro variable is above a certain value, then carry out some additional code.

%macro condition;

%if &pctval >0.5 %then %do;

Procedure ………

%else %do;

Procedure ……

run;

%end;

Upvotes: 0

Views: 2253

Answers (4)

Reeza
Reeza

Reputation: 21294

If all you're interested in is the % successful, capture that number directly in a SQL query into a macro variable. Less passes of the data is preferable.

proc sql noprint;
    select sum(status='Successful')/count(*) into :pct_successful
    from have;
quit;

%put &pct_successful.;

Then you can use it as desired later on to drive further processing.

Upvotes: 2

Lawrence
Lawrence

Reputation: 81

If you just want to perform your condition macro for "Successful":

(1) Output your proc freq into a table using /out=freq.

(2) Use a proc sql to get the percent value into a macro variable.

(3) Perform your condition macro.

/* (1) */
proc freq data=have;
    tables status /out=freq;
run;

/* (2) */
proc sql;
    select
    PERCENT/100 into: pctval
    from freq (where=(status = "Successful"));
quit;

/* (3) */
%macro condition;   
    %if &pctval >0.5 %then %do;
    
        Procedure ………
    
    %else %do;
    
        Procedure ……
    
    %end;
%mend;

Upvotes: 2

Lawrence
Lawrence

Reputation: 81

If you want to perform your condition macro for multiple status:

(1) Output your proc freq into a table using /out=freq.

(2) Use a proc sql to get the status and percent values into a macro variable.

(3) Perform your condition macro for each value of status, using the %let to get the name of the status we're on and the percent value for it.

/* (1) */
proc freq data=have;
    tables status /out=freq;
run;

/* (2) */
proc sql;
    select
     count(*)
    ,Status
    ,PERCENT
    into
     :num_status
    ,:status_list  separated by "|"
    ,:percent_list separated by "|"
    from freq (where=(Status="Successful"));
quit;

/* (3) */
%macro condition;   
    %do i=1 %to num_status;
        %let status = %scan(%str(&status_list.),&i.,%str(|),m);
        %let pctval = %scan(%str(&percent_list.),&i.,%str(|),m);
        
        %if &pctval >0.5 %then %do;
        
            Procedure ………
        
        %else %do;
        
            Procedure ……
        
        %end;
        
    %end;
%mend;

Let me know if you need something different.

Upvotes: 2

Kermit
Kermit

Reputation: 3117

Output the results from the FREQ procedure to a table and use the SQL procedure to store it in a macro-variable for later use.

data have;
format status $20.;
do i=1 to 500;
    status="Successful";
    output;
end;
do i=501 to 600;
    status="Unsuccessful";
    output;
end;
do i=601 to 625;
    status="Unknown";
    output;
end;
drop i;
run;

proc freq data=have noprint;
table status / out=want TOTPCT list;
quit;

proc sql noprint;
select percent/100 into :successpct
from want
where status = "Successful";
quit;

%macro do_smth(treshold);
    %if %sysevalf(&successpct. > &treshold.) %then %do;
        %put "Success percentage above &treshold. treshold";
        /* ... do smth ... */
    %end;
    %else %do;
        %put "Success percentage below &treshold. treshold";
        /* ... do smth else ... */
    %end;
%mend;

%do_smth(0.79);

Output:
"Success percentage above 0.79 treshold"

Upvotes: 3

Related Questions