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

I am running into some issues using a macro code-I want to count/summing the number of variables in a list for an entire dataset. Say for instance my dataset looks like the following:

ID                    Factors

123                   A3, A4, A5

233                   A3

451                   A4, A5, F5

567                   F5

112                   A1

344                  A3, A5

557                  A4

My goal is to create one large variable list (A3, A4, A5, A3, A4, A5, F5, F5, A1, A3, A5, A4) to then count the frequency of each factor (I know it will not total 100% since more than one attribute can be reported per ID) to get this:

Factor            Value

A1                     1

A3                     3

A4                     3

A5                     3

F5                     2

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Here is an alternative using Hash object:

data have;

input ID $3. Factors :$&20.;

cards;

123                   A3, A4, A5

233                   A3

451                   A4, A5, F5

567                   F5

112                   A1

344                  A3, A5

557                  A4

;

data _null_;

if _n_=1 then do;

dcl hash h(ordered:'a');

h.definekey('Factor');

      h.definedata('Factor','Value');

      h.definedone();

length factor $ 8;

call missing (factor,value);

end;

set have end=last;

do i=1 by 1 while (not missing(scan(factors,i)));

      factor=scan(factors,i);

       rc=h.find();

       if rc ne 0 then value=0;

       value=value+1;

       rc=h.replace();

     end;

if last then h.output(dataset:'want');

run;

Haikuo      

View solution in original post

4 REPLIES 4
art297
Opal | Level 21

Couldn't you just create a longer dataset and use proc freq?  e.g.,

data have;

  informat Factors $30.;

  input ID Factors &;

  cards;

123                   A3, A4, A5

233                   A3

451                   A4, A5, F5

567                   F5

112                   A1

344                  A3, A5

557                  A4

;

data need (drop=factors);

  set have;

  _n_=1;

  do while (scan(factors,_n_,',') ne "");

    factor=strip(scan(factors,_n_,','));

    _n_ + 1;

    output;

  end;

run;

proc freq data=need;

  tables factor;

run;

Haikuo
Onyx | Level 15

Here is an alternative using Hash object:

data have;

input ID $3. Factors :$&20.;

cards;

123                   A3, A4, A5

233                   A3

451                   A4, A5, F5

567                   F5

112                   A1

344                  A3, A5

557                  A4

;

data _null_;

if _n_=1 then do;

dcl hash h(ordered:'a');

h.definekey('Factor');

      h.definedata('Factor','Value');

      h.definedone();

length factor $ 8;

call missing (factor,value);

end;

set have end=last;

do i=1 by 1 while (not missing(scan(factors,i)));

      factor=scan(factors,i);

       rc=h.find();

       if rc ne 0 then value=0;

       value=value+1;

       rc=h.replace();

     end;

if last then h.output(dataset:'want');

run;

Haikuo      

CaraJ
Calcite | Level 5

Thank you so much Hai.kuo

data_null__
Jade | Level 19

You use the terms "variable list" and "factor".   What do you mean?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 884 views
  • 3 likes
  • 4 in conversation