BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RTelang
Fluorite | Level 6

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);

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

9 REPLIES 9
Astounding
PROC Star

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?

RTelang
Fluorite | Level 6

@Astounding i want to replace the value.

Astounding
PROC Star

This doesn't show me what the outcome should be.  You will need to provide an example of the results that you want.

error_prone
Barite | Level 11

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;

 

 

 

 

 

RTelang
Fluorite | Level 6

@error_prone want  to check the occurrences of column variable values.

ballardw
Super User

Show some input data and expected results.

Ksharp
Super User
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;


Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1163 views
  • 0 likes
  • 5 in conversation