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.
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
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;
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
Thank you so much Hai.kuo
You use the terms "variable list" and "factor". What do you mean?
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 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.