0

Is it possible to create a macro variable that is set to the frequency variable produced by proc freq? I'm trying to create a variable that will equal the number of times each last name appears in a data set. For example, Smithe may appear 3 times while Jackson appears only 2 times. I want to capture that value and use it.

Sarah
  • 93
  • 1
  • 1
  • 8

1 Answers1

3

You can either use call symput in a following data step (after outputting the proc freq to a dataset with /out= and/or using ods output), or you can avoid the proc freq and do the frequency with proc sql via select into, which will create a macro variable.

proc sql;
  select sex, count(1) 
    into :sex separated by ' ',
         :count separated by ' '
    from sashelp.class
    group by sex;
quit;

This makes something approximating a pair of macro variable arrays (one with the values one with the counts). If you want to use the name (or whatever) as the macro variable name, use the first option (follow-on datastep with call symput) as that lets you name the macro variable.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • 1
    Slightly off topic, but, I hadn't seen the notation `count(1)` used before which got me to wondering if there is a difference between that and `count(*)`. I guess not: http://stackoverflow.com/questions/1221559/count-vs-count1 . – Robert Penridge Aug 03 '15 at 16:40
  • @RobertPenridge It is implementation specific, I believe; I think in SAS is similar to SQL Server (there is no difference). I have been told by many Oracle DBAs that in Oracle it does make a difference in execution speed, though. As Oracle was where I first really learned SQL, that stuck with me. (And apparently it no longer has an effect in Oracle, but since I learned this years ago from people who learned it years ago...) – Joe Aug 03 '15 at 16:41
  • Honestly I probably should stop using count(1) since it's basically propagating an urban myth at this point, but since everyone knows old programmers never change their tricks... – Joe Aug 03 '15 at 16:46