sas how to save multiple proc sql select output from a %do loop?

[xinyao]

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!

[VDD]

try merging the output datasets by race.

https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-in-sas/

[xinyao]

Thank you for your help, it's really helpful!

[Reeza]

Looks like you're doing a transpose via SQL, I would suggest a PROC TRANSPOSE instead that would be significantly faster.

[novinosrin]

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

[DBailey]

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;

[xinyao]

Thank you for your quick response!
sorry I didn't mention some gn_name are two words with space, this cause error message, can you help me for this part?

Can you also give reference to me, so I can better understand this code. Thank you so much!

[DBailey]

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.

[xinyao]

I use
data work.have;
set test (keep= gn_name id race);
gnrc_name= strip(gn_name);
run;

but still got error message,

cdef like below:

count(distinct case when gn_name='Pyrilamine ' then gn_name end) as Pyrilamine____________________
count(distinct case when gn_name='Piperonyl Butoxide ' then gn_name end) as Piperonyl_Butoxide_____________
count(distinct case when gn_name='Zinc SulfateHCL ' then gn_name end) as Zinc_SulfateHCL__________________

Thank you so much!

[xinyao]

I use
data work.have;
set test (keep= gn_name id race);
gn_name= strip(gn_name);
run;

but still got error message,

cdef like below:

count(distinct case when gn_name='Pyrilamine ' then gn_name end) as Pyrilamine____________________
count(distinct case when gn_name='Piperonyl Butoxide ' then gn_name end) as Piperonyl_Butoxide_____________
count(distinct case when gn_name='Zinc SulfateHCL ' then gn_name end) as Zinc_SulfateHCL__________________

Thank you so much!

[xinyao]

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

[xinyao]

I put trim(gnrc_name) into
then it's work well!
but if gn_name like Piperonyl/Butoxide, how can replace the blanks with underscores same time replace the "/" with underscores, Thank you so much!

[DBailey · Accepted solution]

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;

[xinyao]

Thank you very much for your patients and help!