topic Re: proc sql count problem in SAS Procedures

proc sql count problem

maomaochong96888_hotmail_com — Fri, 06 Sep 2013 13:47:26 GMT

hi!

I have a question.

there is a dataset.

data t;

input group pt $;

datalines;

1 a

1 b

2 u

2 r

3 t

4 q

6 c

;

quit;

Actually I have 6 groups. but for now group 5 have no patients and it will have patients later.

Now I need to count how many patients in each group.

proc sql noprint;

select count(pt) into: n1 - :n6 from t group by group;

quit;

%put &n1 &n2 &n3 &n4 &n5 &n6;

the log said " WARNING: Apparent symbolic reference N6 not resolved."

I understand why there is such a SAS warning there because SAS doesn't know there is a group 5 at all.

then I have to rewrite it one group by one group

proc sql noprint;

select count(pt) into: n1 from t where group = 1;

...

select count(pt) into: n5 from t where group = 5;

select count(pt) into: n6 from t where group = 6;

quit;

It works! But now there are only 6 group, how about if there are many groups? I already thought about this problem for a long time...

so if someone can tell me how to solve such problems just by using proc sql?

thank you very much.

Re: proc sql count problem

yaswanthj — Fri, 06 Sep 2013 14:17:59 GMT

Hi..

Please follow as bellow

data t;

input group pt $;

datalines;

1 a

1 b

2 u

2 r

3 t

4 q

6 c

;

quit;

proc sql noprint;

select count(distinct(pt)) into :count separated by ',' from t;

select count(pt) into: n1 - :n&count. from t group by group;

quit;

You should have to count the values first and assign that to one variable. and use that count variable where ever you want. in the above program, if you add any record, it should take the count and will create the macro variables as many you want.

i think this is enough to do as your requirement.

Thanks,

Yaswanth

Re: proc sql count problem

AncaTilea — Fri, 06 Sep 2013 14:21:12 GMT

Hi, you could try something like this:

proc sql noprint;

insert into t values (5, NULL )

values (7, NULL);

select count(pt) into: n1 - :n7 from t group by grp;

quit;

Note I changed the name of your "group" variable to "grp".

...

Anca.

%put &n1 &n2 &n3 &n4 &n5 &n6 &n7.;

Re: proc sql count problem

maomaochong96888_hotmail_com — Fri, 06 Sep 2013 15:00:05 GMT

Thank you both of you

but they do not work!

Anca: I am not allowed to insert anything into the dataset manually....

yaswanthj: the problem is SAS doesn't know there is group 5 because there is no such records in group 5 so far...

Re: proc sql count problem

yaswanthj — Fri, 06 Sep 2013 15:06:34 GMT

As per my knowledge, we can create the macro variables with existing one`s or can create inserting some thing. im not sure, we can assign the values to the macro variables using non existing values..

Re: proc sql count problem

Jagadishkatam — Fri, 06 Sep 2013 15:35:21 GMT

hope the below code meets your criteria

data t;

input group pt $;

datalines;

1 a

1 b

2 u

2 r

3 t

4 q

6 c

;

quit;

/*create a dummy dataset with groups and give the values as 0*/

data dummy;

do group=1 to 6;

count=0;

output;

end;

run;

proc sql;

/* get the distinct count of the pt in each group*/

create table test as select count(distinct pt) as count ,group from t group by group;

/*merge the counts of the pt in each group with the dummy dataset which has all the groups*/

create table test2 as select coalesce(a.count,b.count) as count,b.group from test as a right join dummy as b on a.group=b.group;

/*generate the macro for each group by the below code*/

select count into: n1-:n&sysmaxlong from test2;

quit;

%put _user_;

Thanks,

Jagadish

Re: proc sql count problem

maomaochong96888_hotmail_com — Fri, 06 Sep 2013 17:28:29 GMT

YES Jagadishkatam It works!

So I have to create a dummy dataset to solve this problem!

Thanks all of you!!!

Re: proc sql count problem

Linlin — Fri, 06 Sep 2013 17:54:06 GMT

data t;
input group pt $;
datalines;
1 a
1 b
2 u
2 r
3 t
4 q
6 c
;
proc summary data=t n;
by group;
output out=temp;
run;

data _null_;
set temp;
call symputx (cats('n',group),_freq_);
run;
%put &n1 &n2 &n3 &n4 &n5 &n6;

Re: proc sql count problem

Tom — Fri, 06 Sep 2013 19:42:38 GMT

Read this paper.

http://www.lexjansen.com/pharmasug/2005/coderscorner/cc22.pdf

Re: proc sql count problem

maomaochong96888_hotmail_com — Fri, 06 Sep 2013 19:58:36 GMT

THANKS LINLIN ~

THANKS TOM!!! This paper is just written for my question!:smileylaugh:

I can always get what I want in the forum!!!

thanks you!