Help using Base SAS procedures

PROC FREQ Query?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

PROC FREQ Query?

I have a dataset of dummy variables that look like this -

(Both tables the same - some problems viewing as image)

ContactVar1Var2Var3Var4Var5Var6
A111111
B


1
1
C111

1
D


11
E
11


F1



1

Capture1.PNG

What I need is to do a frequency count of all the variable pairings so that I get a table that looks like this -

Capture2.PNG

VariableVar1Var2Var3Var4Var5Var6
Var1
22112
Var22
3112
Var323
112
Var4111
21
Var51112
1
Var622211

Does anyone have any ideas how to achieve this?

Thanks.


Accepted Solutions
Solution
‎03-29-2012 05:52 AM
Super User
Posts: 9,681

PROC FREQ Query?

It is really more difficult than i imaged.

data have;
input contact $ var1-var6;
cards;
a 1 1 1 1 1 1
b . . . 1 . 1
c 1 1 1 . . 1
d . . . 1 1 .
e . 1 1 . . .
f 1 . . . . 1
;
run;
data temp(keep= name1 name2);
 set have;
 length name1 name2 $20 ;
 array v{*} var1-var6;
 do i=1 to dim(v)-1;
  do j=i+1 to dim(v);
   if v{i}=1 and v{j}=1 then do;
                               name1=vname(v{i});
                               name2=vname(v{j});
                               output;
                              end;
  end;
 end;
run;
proc freq data=temp noprint;
 tables name1*name2/out=x(drop=percent) nopercent nocum;
run;
proc sql noprint;
 select quote(trim(name)) into : list separated by ','
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and name like 'var%';
quit;

data xx(keep=name1 name2);
 length name1 name2 $20 ;
 do name1=&list ;
  do name2=&list;
    output;
  end;
 end;
run;
proc sort data=xx;by name1 name2;run;
data t;
 merge xx x;
 by name1 name2;
 _name1=name1;_name2=name2;
run;
data t;
 set t;
 call sortc(_name1,_name2);
run;
data tt(drop=_:);
 if _n_ eq 1 then do;
  if 0 then set t;
  declare hash ha(dataset:'t');
   ha.definekey('_name1','_name2');
   ha.definedata('count');
   ha.definedone();
 end;
 set t;
 count=.;
 rc=ha.find();
run;

proc transpose data=tt out=want(drop=_:);
 by name1;
 id name2;
 var count;
run;

Ksharp

View solution in original post


All Replies
Frequent Contributor
Posts: 138

PROC FREQ Query?

No able to view your images.

Solution
‎03-29-2012 05:52 AM
Super User
Posts: 9,681

PROC FREQ Query?

It is really more difficult than i imaged.

data have;
input contact $ var1-var6;
cards;
a 1 1 1 1 1 1
b . . . 1 . 1
c 1 1 1 . . 1
d . . . 1 1 .
e . 1 1 . . .
f 1 . . . . 1
;
run;
data temp(keep= name1 name2);
 set have;
 length name1 name2 $20 ;
 array v{*} var1-var6;
 do i=1 to dim(v)-1;
  do j=i+1 to dim(v);
   if v{i}=1 and v{j}=1 then do;
                               name1=vname(v{i});
                               name2=vname(v{j});
                               output;
                              end;
  end;
 end;
run;
proc freq data=temp noprint;
 tables name1*name2/out=x(drop=percent) nopercent nocum;
run;
proc sql noprint;
 select quote(trim(name)) into : list separated by ','
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and name like 'var%';
quit;

data xx(keep=name1 name2);
 length name1 name2 $20 ;
 do name1=&list ;
  do name2=&list;
    output;
  end;
 end;
run;
proc sort data=xx;by name1 name2;run;
data t;
 merge xx x;
 by name1 name2;
 _name1=name1;_name2=name2;
run;
data t;
 set t;
 call sortc(_name1,_name2);
run;
data tt(drop=_:);
 if _n_ eq 1 then do;
  if 0 then set t;
  declare hash ha(dataset:'t');
   ha.definekey('_name1','_name2');
   ha.definedata('count');
   ha.definedone();
 end;
 set t;
 count=.;
 rc=ha.find();
run;

proc transpose data=tt out=want(drop=_:);
 by name1;
 id name2;
 var count;
run;

Ksharp

New Contributor
Posts: 3

PROC FREQ Query?

Awesome!! And actually for what I need to do I can simply take the table X created at the top and project solved. Cheers!

Super Contributor
Posts: 276

PROC FREQ Query?

Great ksharp....

amazing...

New Contributor
Posts: 3

PROC FREQ Query?

He is good!

Respected Advisor
Posts: 3,777

Re: PROC FREQ Query?

I borrowed your data step to create HAVE.

data have;

   input contact $ var1-var6;

   cards;

a 1 1 1 1 1 1

b . . . 1 . 1

c 1 1 1 . . 1

d . . . 1 1 .

e . 1 1 . . .

f 1 . . . . 1

;;;;

   run;

ods listing close;

ods output list=list(keep=table frequency);

proc freq;

   tables (var1-var6)*(var1-var6) / list;

   run;

ods listing;    

data list;

   set list;

   row=scan(table,2);

   col=scan(table,-1);

   if row eq col then frequency=.;

   run;

proc sort data=list;

   by row col;

   run;

proc transpose out=square(drop=_name_);

   by row;

   id col;

   var frequency;

   run;

proc print;

   run;

proc compare base=want compare=square;

   run;

Obs    row         var1        var2        var3        var4        var5        var6

                                                                                  

1     var1           .           2           2           1           1           3

2     var2           2           .           3           1           1           2

3     var3           2           3           .           1           1           2

4     var4           1           1           1           .           2           2

5     var5           1           1           1           2           .           1

6     var6           3           2           2           2           1           .

Message was edited by: data _null_

Super User
Posts: 9,681

Re: PROC FREQ Query?

Great NULL.

Respected Advisor
Posts: 4,646

Re: PROC FREQ Query?

Yet another way:

data have;
input contact $ var1-var6;
cards;
a 1 1 1 1 1 1
b . . . 1 . 1
c 1 1 1 . . 1
d . . . 1 1 .
e . 1 1 . . .
f 1 . . . . 1
;


proc transpose data=have out=havelist(where=(col1 is not missing));
by contact notsorted;
var var:;
run;

proc sql;
create table haveCounts as
select h1._name_ as variable, h2._name_ as col, count(*) as n
from haveList as h1 inner join haveList as h2
on h1.contact=h2.contact and h1._name_ ne h2._name_
group by h1._name_, h2._name_
order by variable;
drop table haveList;
quit;

proc transpose data=haveCounts out=want(drop=_NAME_);
by variable;
var n;
id col;
run;

/* At this point you have the counts table. Now for presentation : */

option missing="";
proc sql;
drop table haveCounts;
select variable label="Variable", var1, var2, var3, var4, var5, var6
from want;
quit;

PG

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 412 views
  • 2 likes
  • 6 in conversation