Using Proc Freq or different method

Question

I am trying to display create data set to make it show that how consumer units participated in at least three of the four quarterly interviews.

I am not sure if its efficient to use proc freq by looking at how many times I see CU_ID come up. but this is my code so far:

data fmli071Q1;
set hw06.fmli071;
QTR = 1;
run;

data fmli072Q2;
set hw06.fmli072;
QTR = 2;
run;

data fmli073Q3;
set hw06.fmli073;
QTR = 3;
run;

data fmli074Q4;
set hw06.fmli074;
QTR = 4;
run;

data fmli2007_bob2;
set fmli071Q1 fmli072Q2 fmli073Q3 fmli074Q4;
run;

proc contents data = fmli2007_bob2;
run;

data memi071Q1;
set hw06.memi071;
QTR = 1;
run;

data memi072Q2;
set hw06.memi072;
QTR = 2;
run;

data memi073Q3;
set hw06.memi073;
QTR = 3;
run;

data memi074Q4;
set hw06.memi074;
QTR = 4;
run;

data memi2007_bob2;
set memi071Q1 memi072Q2 memi073Q3 memi074Q4;
run;

proc sort data=fmli2007_bob2;
by CU_ID;
run;

proc sort data=memi2007_bob2;
by CU_ID;
run;

data ce2007_bob2;
merge fmli2007_bob2 memi2007_bob2
by CU_ID;
run;

Now I am at a part where I need to display how many consumer units participated in at least three of the four quarterly interviews in 2007? How should I do this by proc freq or a different method I want to create a new data step call at least_three_bob2 that will only show IDS of consumer units that participated in at least three of the four quarterly interviews in 2007. Then eventually print it out

data atleast_three_bob2;
set ce2007_bob2;
run;

Richard Richard · Accepted Answer · 2018-03-14T05:06:38

Proc SQL can perform data aggregation and frequency counts based on complex critera.

data have;
  do quarter = 1 to 4;
    do id = 1 to 100;
      if quarter > 0 and mod(id,7) = 0 then 
        output;
      else
      if quarter > 1 and mod(id,13) = 0 then 
        output;
      else if quarter > 2 then 
        output;    
    end;
  end;
run;

proc sql;
  create table want(label="ids and quarters of ids appearing in >= 3 quarters") as 
  select quarter, id
  from have
  group by id
  having count(distinct quarter) >= 3
  order by id, quarter
  ;

  create table want2(label="frequency counts for number of ids having >= 3 quarters") as
  select quarter_count, count(*) as id_count 
  from
  (
    select count(*) as quarter_count
    from want
    group by id
  )
  group by quarter_count
  ;

Using Proc Freq or different method

1 Answers