my data like:
id gn_name race
1 A 0
2 A 1
3 A 3
......
%macro loop(values);
%let n=100;
%do i = 1 %to &n;
%let value=%qscan(&values,&i,%str(,));
%let name = d_&i.;
%put &value;
proc sql;
create table &name as
select *
from data
where gn_name = "&value";
proc sql;
select race, count (distinct id) as n_&name into:race,:n
from &name group by race;
select race, count (distinct id) as n_&name
from &name group by race;
%end;
%mend;
%loop(%nrstr(A,B,C,D, E....))
result as below:
race name_1
0 1
1 22
2 1
3 2
4 0
5 0
race name_2
0 5
1 25
2 4
3 4
4 1
5 0
.......
I want results as
race name_1 name_2 name_3 ......
0 1 5 ......
1 22 25 ......
2 1 4 ......
3 2 4 ......
4 0 1 ......
5 0 0 ......
what should I do, please help me for this! thank you!
had to add some string handling...
data work.have;
length
id 8
gn_name $100
race 8
;
infile cards;
input id gn_name $ 3-5 race ;
cards;
1 A 1 0
2 A 1 1
3 A 1 3
;
run;
proc sql;
insert into have (id, gn_name, race) values (1, 'Zinc SulfateHCL', 1);
insert into have (id, gn_name, race) values (2, 'Piperonyl Butoxide', 1);
insert into have (id, gn_name, race) values (3, 'Pyrilamine', 2);
select
cat("count(distinct case when trim(left(gn_name))='",trim(left(gn_name)),"' then gn_name end) as ",translate(trim(gn_name),'_',' ')) length=500 as cdef
into
:cdefs separated by ', '
from (select distinct gn_name from work.have);
%put cdefs=&cdefs;
create table want as
select
race
,&cdefs
from
work.have
group by race;
quit;
try merging the output datasets by race.
https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-in-sas/
Thank you for your help, it's really helpful!
any rigid reason to use a macro in the first place? I feel you don't need a macro at all. If you are ok with my opinion, please post a comprehensive sample, what you intend to do and the output you want. Thank you
I don't think you need a macro....but you can use a macro variable to get the count distinct...
data work.have;
input id gn_name $ race;
datalines;
1 A 0
2 A 1
3 A 3
;;
run;
proc sql;
select
cat("count(distinct case when gn_name='",trim(gn_name),"' then gn_name end) as ",gn_name) as cdef
into
:cdefs separated by ', '
from (select distinct gn_name from work.have);
create table want as
select
race
,&cdefs
from
work.have
group by race;
quit;
You can just replace the blanks with underscores...
data work.have;
infile cards;
input id gn_name $ 3-5 race ;
cards;
1 A 1 0
2 A 1 1
3 A 1 3
;
run;
proc sql;
select
cat("count(distinct case when gn_name='",gn_name,"' then gn_name end) as ",translate(gn_name,'_',' ')) as cdef
into
:cdefs separated by ', '
from (select distinct gn_name from work.have);
%put cdefs=&cdefs;
create table want as
select
race
,&cdefs
from
work.have
group by race;
quit;
All the code is doing is constructing the specific sql syntax to count what you want to count and then storing that in a macro variable. You can print out the value of the macro variable in the code to see what it is doing.
data like this,
obs id gn_name race
1 1 Zinc SulfateHCL 1
2 1 Zinc SulfateHCL 1
3 1 Zinc SulfateHCL 1
4 1 Zinc SulfateHCL 1
5 1 Zinc SulfateHCL 1
6 1 Zinc SulfateHCL 1
7 1 Zinc SulfateHCL 1
8 1 Zinc SulfateHCL 1
9 1 Zinc SulfateHCL 1
10 1 Zinc SulfateHCL 1
11 1 Zinc SulfateHCL 1
12 1 Zinc SulfateHCL 1
13 1 Zinc SulfateHCL 1
14 1 Zinc SulfateHCL 1
15 1 Zinc SulfateHCL 1
16 1 Zinc SulfateHCL 1
17 1 Zinc SulfateHCL 1
18 1 Zinc SulfateHCL 1
19 1 Zinc SulfateHCL 1
20 1 Zinc SulfateHCL 1
21 1 Zinc SulfateHCL 1
22 1 Zinc SulfateHCL 1
23 1 Zinc SulfateHCL 1
24 1 Zinc SulfateHCL 1
25 1 Zinc SulfateHCL 1
26 1 Zinc SulfateHCL 1
27 1 Zinc SulfateHCL 1
28 1 Zinc SulfateHCL 1
29 1 Zinc SulfateHCL 1
30 1 Zinc SulfateHCL 1
31 1 Zinc SulfateHCL 1
32 1 Zinc SulfateHCL 1
33 1 Zinc SulfateHCL 1
34 1 Zinc SulfateHCL 1
35 1 Zinc SulfateHCL 1
36 1 Zinc SulfateHCL 1
37 1 Zinc SulfateHCL 1
38 1 Zinc SulfateHCL 1
39 1 Zinc SulfateHCL 1
40 1 Zinc SulfateHCL 1
41 1 Zinc SulfateHCL 1
42 1 Zinc SulfateHCL 1
43 1 Zinc SulfateHCL 1
44 1 Zinc SulfateHCL 1
45 1 Zinc SulfateHCL 1
46 1 Zinc SulfateHCL 1
47 1 Zinc SulfateHCL 1
48 1 Zinc SulfateHCL 1
49 1 Zinc SulfateHCL 1
50 1 Zinc SulfateHCL 1
51 1 Zinc SulfateHCL 1
52 1 Zinc SulfateHCL 1
53 1 Zinc SulfateHCL 1
54 1 Zinc SulfateHCL 1
55 1 Zinc SulfateHCL 1
56 1 Zinc SulfateHCL 1
57 1 Zinc SulfateHCL 1
58 1 Zinc SulfateHCL 1
59 1 Zinc SulfateHCL 1
60 1 Zinc SulfateHCL 1
61 1 Zinc SulfateHCL 1
62 1 Zinc SulfateHCL 1
63 1 Zinc SulfateHCL 1
64 1 Zinc SulfateHCL 1
65 1 Zinc SulfateHCL 1
66 1 Zinc SulfateHCL 1
67 1 Zinc SulfateHCL 1
68 1 Zinc SulfateHCL 1
69 1 Zinc SulfateHCL 1
70 1 Zinc SulfateHCL 1
71 1 Zinc SulfateHCL 1
72 1 Zinc SulfateHCL 1
73 1 Zinc SulfateHCL 1
74 1 Zinc SulfateHCL 1
75 1 Zinc SulfateHCL 1
76 1 Zinc SulfateHCL 1
77 1 Zinc SulfateHCL 1
78 1 Zinc SulfateHCL 1
79 1 Zinc SulfateHCL 1
80 1 Zinc SulfateHCL 1
81 1 Zinc SulfateHCL 1
82 1 Zinc SulfateHCL 1
83 2 Piperonyl Butoxide 5
84 2 Piperonyl Butoxide 5
85 2 Piperonyl Butoxide 5
86 2 Piperonyl Butoxide 5
87 2 Piperonyl Butoxide 5
88 2 Piperonyl Butoxide 5
89 2 Piperonyl Butoxide 5
90 2 Piperonyl Butoxide 5
91 2 Piperonyl Butoxide 5
92 2 Piperonyl Butoxide 5
93 2 Piperonyl Butoxide 5
94 2 Piperonyl Butoxide 5
95 2 Piperonyl Butoxide 5
96 2 Piperonyl Butoxide 5
97 2 Piperonyl Butoxide 5
98 2 Piperonyl Butoxide 5
99 2 Piperonyl Butoxide 5
100 2 Piperonyl Butoxide 5
101 3 Pyrilamine 2
102 3 Pyrilamine 2
103 3 Pyrilamine 2
104 3 Pyrilamine 2
105 3 Pyrilamine 2
106 3 Pyrilamine 2
107 3 Pyrilamine 2
108 3 Pyrilamine 2
109 3 Pyrilamine 2
had to add some string handling...
data work.have;
length
id 8
gn_name $100
race 8
;
infile cards;
input id gn_name $ 3-5 race ;
cards;
1 A 1 0
2 A 1 1
3 A 1 3
;
run;
proc sql;
insert into have (id, gn_name, race) values (1, 'Zinc SulfateHCL', 1);
insert into have (id, gn_name, race) values (2, 'Piperonyl Butoxide', 1);
insert into have (id, gn_name, race) values (3, 'Pyrilamine', 2);
select
cat("count(distinct case when trim(left(gn_name))='",trim(left(gn_name)),"' then gn_name end) as ",translate(trim(gn_name),'_',' ')) length=500 as cdef
into
:cdefs separated by ', '
from (select distinct gn_name from work.have);
%put cdefs=&cdefs;
create table want as
select
race
,&cdefs
from
work.have
group by race;
quit;
Thank you very much for your patients and help!
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.