Help using Base SAS procedures

proc sql count problem

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

proc sql count problem

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.


Accepted Solutions
Solution
‎09-06-2013 11:35 AM
Trusted Advisor
Posts: 1,137

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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

Thanks,
Jag

View solution in original post


All Replies
Contributor
Posts: 70

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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

Super Contributor
Posts: 543

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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.;

Occasional Contributor
Posts: 16

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

Thank you both of youSmiley Happy

but they do not work!

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

Contributor
Posts: 70

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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..

Solution
‎09-06-2013 11:35 AM
Trusted Advisor
Posts: 1,137

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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

Thanks,
Jag
Occasional Contributor
Posts: 16

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

YES

Super Contributor
Posts: 1,636

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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;

Super User
Super User
Posts: 7,076

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com
Occasional Contributor
Posts: 16

Re: proc sql count problem

Posted in reply to maomaochong96888_hotmail_com

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!

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 494 views
  • 6 likes
  • 6 in conversation