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.
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
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
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.;
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...
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..
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
YES Jagadishkatam It works!
So I have to create a dummy dataset to solve this problem!
Thanks all of you!!!
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;
Read this paper.
http://www.lexjansen.com/pharmasug/2005/coderscorner/cc22.pdf
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.