topic Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL in SAS Programming

Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

kksss — Fri, 04 Mar 2016 15:54:44 GMT

ID var1

1 x

1 y

1 z

1 x

2 z

2 x

3 h

3 a

Ideally, I want it to be like either

ID var1 var1_count count_of_x

1 x 4 2

1 y 4 2

1 z 4 2

1 x 4 2

2 z 2 1

2 x 2 1

3 h 3 1

3 x 3 1

3 a 3 1

or this form will also work

ID var1_count count_of_x

1 4 2

2 2 1

3 3 1

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

FreelanceReinh — Fri, 04 Mar 2016 16:19:10 GMT

Try this:

/* Create test data */
data have;
input ID var1 $;
cards;
1 x 
1 y
1 z
1 x
2 z
2 x
3 h
3 x
3 a
;

/* Long form */
data want;
do until(last.id);
  set have;
  by id;
  var1_count=sum(var1_count, 1); 
  count_of_x=sum(count_of_x, var1='x');
end;
do until(last.id);
  set have;
  by id;
  output;
end;
run;

proc print data=want noobs;
run;

/* Aggregated form */
data want2;
do until(last.id);
  set have;
  by id;
  var1_count=sum(var1_count, 1); 
  count_of_x=sum(count_of_x, var1='x');
end;
drop var1;
run;

proc print data=want2 noobs;
run;

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

LinusH — Fri, 04 Mar 2016 16:36:57 GMT

Always puzzled by requests like "...not using SQL". Shouldn't we use the most appropriate tool?
I think SQL in many situations are easier to code, for others to understand, in case of external RDBMS it will automatically being pushed to the source, and in SAS can be executed with multi threading (wich the data step won't).

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

kksss — Fri, 04 Mar 2016 16:39:21 GMT

well... then throw me how it can be done with proc sql, and I'll see if I like it...

My reason is because, I have to pass down my code to colleagues who doesn't use sql much... so it's easier for them to understand what is going on.

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

FreelanceReinh — Fri, 04 Mar 2016 16:45:26 GMT

@LinusH: I remember the "dark ages" when I would have preferred a non-SQL solution, too, as I would have felt less confident to adapt and maintain such "advanced" code.

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

LinusH — Fri, 04 Mar 2016 18:05:10 GMT

@FreelanceReinhard 😃
@kksss:
Select id, count (*) as count, sum(case var1 when 'x' then 1 else 0 end) as count_X
From have
Group by id;

No 1 of my guide when to chose data step or SQL:
Are my task row (data step) or column (SQL) oriented?

I'm categorising this task as column oriented.

Cheers
😆

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

FreelanceReinh — Fri, 04 Mar 2016 18:25:08 GMT

For the aggregated output dataset, of course, that's fine (I would have shortened the definition of count_X to sum(var1='x'), though). However, to obtain the result in "long form" with PROC SQL alone, would require additional measures to ensure that the order of the observations within one ID is preserved (if this was important).

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

kksss — Fri, 04 Mar 2016 18:31:43 GMT

your count_of_x doesn't work. it's showing me

id var1 count of x

1 x 1

1 y 2

2 z 3

2 x 4

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

FreelanceReinh — Fri, 04 Mar 2016 18:38:57 GMT

Hi @kksss,

If you're talking about my suggested solution, I cannot replicate your finding. My data step for the "long form" results in:

              var1_    count_
ID    var1    count     of_x

 1     x        2         1
 1     y        2         1
 2     z        2         1
 2     x        2         1

as it should.

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

kksss — Fri, 04 Mar 2016 20:51:12 GMT

I don't know what i did wrong but it's simply just not working like you pasted

the var count is working fine, but the count of x is not.

it's not scaning through x, but indexing the rows by id.

I have attached a screenshot of my out put.

here is my code regarding to those output

data test3;
do until(last.student_id);
set test1;
by student_id;
major_count=sum(major_count, 1);
CSBS_count=sum(CSBS_count, Major_College="Social & Beh Science");
end;
do until(last.student_id);
set test1;
by student_id;
output;
end;
run;

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

kksss — Fri, 04 Mar 2016 21:28:00 GMT

the proc sql works fine however, so i guess I can live with that.

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

FreelanceReinh — Fri, 04 Mar 2016 21:29:52 GMT

I've tried to replicate your issue using the below test data:

data test1;
input student_id major_college $50.;
cards;
1 Social & Beh Science
1 Nondegree Seeking
1 Health
2 Social & Beh Science
2 Social & Beh Science
;

The result of your data step is:

student_                            major_    CSBS_
   id       major_college            count    count

    1       Social & Beh Science       3        1
    1       Nondegree Seeking          3        1
    1       Health                     3        1
    2       Social & Beh Science       2        2
    2       Social & Beh Science       2        2

which seems to be correct. Do you also obtain the above result with my test data?

If so, we should take a closer look at your dataset TEST1.

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

LinusH — Fri, 04 Mar 2016 22:17:21 GMT

The long form violates the 2nd normal form. If one is to mix aggregates and details, that should be done in a report - not in a permanent data store.

The aggregation of boolean expression is quite neat and have a shorter syntax, but it's SAS specific, and maybe not that as intuitively understood.

Re: Count total IDS and distinct Values and put in two count variable Prefer not Proc SQL

FreelanceReinh — Fri, 04 Mar 2016 22:29:12 GMT

@LinusH: Fully agreed.