Hello:
I have a program shown below. To get the macro Ns, I need to write twenty times 'array', 'call symputx' 'proc transpose'. My sample data set is shown in the attachment, it only shows three groups 'bd_dx', 'state_id' and 'if_img'. However I have 20 more groups in the actural sample. Is there a way to approach this more efficient? Maybe create a macro varialbe list to refere all of these groups? Please help. Thanks.
data test;
input Name $ bd_dx_1-bd_dx_6 State_ID_1-State_ID_4 If_img_1-If_img_8;
cards;
A 0.57 0.015 0.371 0.927 0.16 0.336 0.153 0.461 0.342 0.056 0.698 0.165 0.019 0.5
0.413 0.184 0.637 0.689
B 0.512 0.811 0.62 0.293 0.42 0.223 0.809 0.893 0.483 0.714 0.836 0.231 0.314 0.057
0.177 0.698 0.44 0.384
C 0.406 0.313 0.672 0.234 0.759 0.328 0.895 0.004 0.659 0.209 0.449 0.927 0.343 0.368
0.776 0.114 0.073 0.316
D 0.995 0.69 0.487 0.491 0.511 0.288 0.411 0.712 0.17 0.055 0.233 0.465 0.264 0.615
0.807 0.614 0.48 0.262
E 0.193 0.144 0.501 0.576 0.033 0.51 0.999 0.302 0.392 0.048 0.852 0.865 0.96 0.31
0.267 0.998 0.9 0.042
F 0.686 0.103 0.617 0.173 0.538 0.209 0.887 0.824 0.035 0.426 0.642 0.998 0.884 0.525
0.737 0.52 0.744 0.292
;
run;
data want;
set test (drop=name);
array bd_dx(*) bd_dx: ;
call symputx('d_bd',dim(bd_dx));
stop;
run;
data temp;
set test (keep=name bd_dx_1-bd_dx_&d_bd);
run;
proc transpose data=temp out=trans1
prefix=bd_dx;
var bd_dx_1-bd_dx_&d_bd;
by Name;
run;
quit;
What is it that you are trying to put into a list?
It looks like you have a number fo variables with numeric prefixes and you want to transpose them?
Assuming that your lists all include one variable that ends with _1 then you could make the list this way.
proc contents data=have out=contents noprint; run;
proc sql noprint ;
select substr(name,1,length(name)-2)
into :list separated by ' '
from contents
where name like '%_1'
;
%let ngroups=&sqlobs;
quit;
Then if you are writing this in a macro you can add %DO loop to transpose the groups.
%do i=1 %to &ngroups;
%let prefix=%scan(&list,&i);
proc transpose data=have(keep=name &prefix.:)
out=group&i (rename=(col1=&prefix) drop=_name_)
;
by name;
var &prefix.: ;
run;
%end;
http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
The macro above may be helpful.
Easiest way to create a macro variable list, methinks, is with proc sql. But you'd have to tell us more about how you want to use it.
Art, CEO, AnalystFinder.com
And supply your example data in a data step. Excel files don't convey the important attributes of SAS datasets. And many of us can't open them from the internet for security reasons.
I have provided sample test data set above. Basely, when I transpose 'bd_dx' involving three steps, list above. However, my actual dataset contains 20 subgroups similar as 'bd_dx', 'state_id', and 'if_img', etc. I need to write twenty steps to do transpose for each subgroup. I am really looking for a macro program to impletement these steps. Thanks.
@ybz12003 Please do not edit your post to change your question after you've had responses. It makes the whole thread not make sense. If you need to add supporting information do so in a new post.
Hello:
I edited my post base on the Kurt request due to the Excel file is not safe to be opened. I provided my sample data set on my oringinal post. I hope it make easier for the people to read. Thanks.
What is it that you are trying to put into a list?
It looks like you have a number fo variables with numeric prefixes and you want to transpose them?
Assuming that your lists all include one variable that ends with _1 then you could make the list this way.
proc contents data=have out=contents noprint; run;
proc sql noprint ;
select substr(name,1,length(name)-2)
into :list separated by ' '
from contents
where name like '%_1'
;
%let ngroups=&sqlobs;
quit;
Then if you are writing this in a macro you can add %DO loop to transpose the groups.
%do i=1 %to &ngroups;
%let prefix=%scan(&list,&i);
proc transpose data=have(keep=name &prefix.:)
out=group&i (rename=(col1=&prefix) drop=_name_)
;
by name;
var &prefix.: ;
run;
%end;
@ybz12003 wrote:
Hello:
I have a program shown below. To get the macro Ns, I need to write twenty times 'array', 'call symputx' 'proc transpose'. My sample data set is shown in the attachment, it only shows three groups 'bd_dx', 'state_id' and 'if_img'. However I have 20 more groups in the actural sample. Is there a way to approach this more efficient? Maybe create a macro varialbe list to refere all of these groups? Please help. Thanks.
data test;
input Name $ bd_dx_1-bd_dx_6 State_ID_1-State_ID_4 If_img_1-If_img_8;
cards;
A 0.57 0.015 0.371 0.927 0.16 0.336 0.153 0.461 0.342 0.056 0.698 0.165 0.019 0.5
0.413 0.184 0.637 0.689
B 0.512 0.811 0.62 0.293 0.42 0.223 0.809 0.893 0.483 0.714 0.836 0.231 0.314 0.057
0.177 0.698 0.44 0.384
C 0.406 0.313 0.672 0.234 0.759 0.328 0.895 0.004 0.659 0.209 0.449 0.927 0.343 0.368
0.776 0.114 0.073 0.316
D 0.995 0.69 0.487 0.491 0.511 0.288 0.411 0.712 0.17 0.055 0.233 0.465 0.264 0.615
0.807 0.614 0.48 0.262
E 0.193 0.144 0.501 0.576 0.033 0.51 0.999 0.302 0.392 0.048 0.852 0.865 0.96 0.31
0.267 0.998 0.9 0.042
F 0.686 0.103 0.617 0.173 0.538 0.209 0.887 0.824 0.035 0.426 0.642 0.998 0.884 0.525
0.737 0.52 0.744 0.292
;
run;
data want;
set test (drop=name);
array bd_dx(*) bd_dx: ;
call symputx('d_bd',dim(bd_dx));
stop;
run;
data temp;
set test (keep=name bd_dx_1-bd_dx_&d_bd);
run;
proc transpose data=temp out=trans1
prefix=bd_dx;
var bd_dx_1-bd_dx_&d_bd;
by Name;
run;
quit;
Are all variables numeric or do you have character ones interspersed in there? Your sample shows all numeric, which means this is trivial to do in array loops. Transpose to long and then re-transpose again.
data test;
input Name $ bd_dx_1-bd_dx_6 State_ID_1-State_ID_4 If_img_1-If_img_8;
cards;
A 0.57 0.015 0.371 0.927 0.16 0.336 0.153 0.461 0.342 0.056 0.698 0.165 0.019 0.5
0.413 0.184 0.637 0.689
B 0.512 0.811 0.62 0.293 0.42 0.223 0.809 0.893 0.483 0.714 0.836 0.231 0.314 0.057
0.177 0.698 0.44 0.384
C 0.406 0.313 0.672 0.234 0.759 0.328 0.895 0.004 0.659 0.209 0.449 0.927 0.343 0.368
0.776 0.114 0.073 0.316
D 0.995 0.69 0.487 0.491 0.511 0.288 0.411 0.712 0.17 0.055 0.233 0.465 0.264 0.615
0.807 0.614 0.48 0.262
E 0.193 0.144 0.501 0.576 0.033 0.51 0.999 0.302 0.392 0.048 0.852 0.865 0.96 0.31
0.267 0.998 0.9 0.042
F 0.686 0.103 0.617 0.173 0.538 0.209 0.887 0.824 0.035 0.426 0.642 0.998 0.884 0.525
0.737 0.52 0.744 0.292
;
run;
data long;
set test;
array flip(*) bd_dx_1 -- if_img_8;
do i=1 to dim(flip);
v_name=vname(flip(i));
v_value=flip(i);
group=prxchange('s/\_\d+$//', 1, trim(v_name));
index=scan(v_name, -1, "_");
output;
end;
keep name group index v_name v_value;
run;
proc sort data=long;
by name index group;
run;
proc transpose data=long out=wide;
by name index;
id group;
var v_value;
run;
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.
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.