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.
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 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.