Hello am trying to write a code to see if the column column value counts are below a user-defined value then the value is replaced with a user defined value.
%macro Lfreq(indsn=,variable=);
proc freq data = &indsn;
tables &variable;
run;
%mend;
%Lfreq(indsn=myfile.freq2,variable=name);
OK, that result is possible. It may cause some confusion later, after your values have been replaced. But it is possible. This example assumes you are dealing with a character variable:
%macro change_value (indsn=, variable=, outdsn=, label=, threshold=);
proc sort data=&indsn;
by &variable;
run;
proc freq data=&indsn;
tables &variable / noprint missing out=counts (keep=&variable count);
run;
data &outdsn;
merge &indsn counts;
by &variable;
if count <= &threshold then &variable="&label";
drop count;
run;
%mend change_value;
%change_value (indsn=have, variable=name, outdsn=want, label=PQ, threshold=2)
It's untested, but should be OK. Give it a shot we'll find out.
What are you trying to replace? Replace the count that prints in the table? Or create a data set with the original variable being replaced?
@Astounding i want to replace the value.
This doesn't show me what the outcome should be. You will need to provide an example of the results that you want.
Why do you want a macro?
Lables are attached to variables not values. You can use a format togehter with an output-procedure to mark counts below a threshold.
proc freq data=sashelp.class;
tables Age / out=work.forum;
run;
%let threshold = 2;
proc format;
value AgeCountMarker
LOW - &threshold = 'yellow'
%eval(&threshold + 1) - HIGH = 'lightgreen'
;
run;
proc print data=work.forum;
var Age;
var Count / style(column)={background=AgeCountMarker.};
run;
@error_prone want to check the occurrences of column variable values.
Show some input data and expected results.
data test; input name$10.; retain dummy ' '; cards; lily lily lily kate kate sam sam sam ; run; proc sql; create table want(drop=dummy) as select *,case when(count(*) gt 2) then 'PQ' else name end as want length=40 from test group by name; quit;
OK, that result is possible. It may cause some confusion later, after your values have been replaced. But it is possible. This example assumes you are dealing with a character variable:
%macro change_value (indsn=, variable=, outdsn=, label=, threshold=);
proc sort data=&indsn;
by &variable;
run;
proc freq data=&indsn;
tables &variable / noprint missing out=counts (keep=&variable count);
run;
data &outdsn;
merge &indsn counts;
by &variable;
if count <= &threshold then &variable="&label";
drop count;
run;
%mend change_value;
%change_value (indsn=have, variable=name, outdsn=want, label=PQ, threshold=2)
It's untested, but should be OK. Give it a shot we'll find out.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.