DATA Step, Macro, Functions and more

occurrences of of column values

Accepted Solution Solved
Reply
Regular Contributor
Posts: 190
Accepted Solution

occurrences of of column values

[ Edited ]

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


Accepted Solutions
Solution
‎09-21-2016 03:56 AM
Super User
Posts: 5,080

Re: count occurrences of of variable values and assign label to

[ Edited ]

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


All Replies
Super User
Posts: 5,080

Re: count occurrences of of variable values and assign label to

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?

Regular Contributor
Posts: 190

Re: count occurrences of of variable values and assign label to

[ Edited ]

@Astounding i want to replace the value.

Super User
Posts: 5,080

Re: count occurrences of of variable values and assign label to

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

Contributor
Posts: 33

Re: count occurrences of of variable values and assign label to

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;

 

 

 

 

 

Regular Contributor
Posts: 190

Re: count occurrences of of variable values and assign label to

[ Edited ]

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

Super User
Posts: 10,483

Re: count occurrences of of variable values and assign label to

Show some input data and expected results.

Regular Contributor
Posts: 190

Re: count occurrences of of variable values and assign label to

[ Edited ]
 
Super User
Posts: 9,671

Re: count occurrences of of variable values and assign label to

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;


Solution
‎09-21-2016 03:56 AM
Super User
Posts: 5,080

Re: count occurrences of of variable values and assign label to

[ Edited ]

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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