BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

9 REPLIES 9
yaswanthj
Calcite | Level 5

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

AncaTilea
Pyrite | Level 9

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 youSmiley Happy

but they do not work!

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

yaswanthj
Calcite | Level 5

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

Jagadishkatam
Amethyst | Level 16

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

YES

Linlin
Lapis Lazuli | Level 10

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;

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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