topic Re: sas how to save multiple proc sql select output from a %do loop? in SAS Programming

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

xinyao — Fri, 12 Oct 2018 16:41:13 GMT

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!

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

VDD — Fri, 12 Oct 2018 16:52:21 GMT

try merging the output datasets by race.

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

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

Reeza — Fri, 12 Oct 2018 16:56:20 GMT

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

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

novinosrin — Fri, 12 Oct 2018 16:56:28 GMT

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

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

DBailey — Fri, 12 Oct 2018 16:57:39 GMT

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;

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

xinyao — Fri, 12 Oct 2018 17:24:56 GMT

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!

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

DBailey — Fri, 12 Oct 2018 17:38:51 GMT

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.

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

xinyao — Fri, 12 Oct 2018 18:08:15 GMT

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!

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

xinyao — Fri, 12 Oct 2018 18:09:56 GMT

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!

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

xinyao — Fri, 12 Oct 2018 18:12:39 GMT

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

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

xinyao — Fri, 12 Oct 2018 18:16:43 GMT

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

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

xinyao — Fri, 12 Oct 2018 18:27:38 GMT

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!

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

DBailey — Fri, 12 Oct 2018 18:30:24 GMT

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;

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

xinyao — Fri, 12 Oct 2018 19:18:12 GMT

Thank you very much for your patients and help!