topic Re: How to create macro variable list? in SAS Programming

How to create macro variable list?

ybz12003 — Wed, 05 Jul 2017 17:40:06 GMT

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;

Re: How to create macro variable list?

Reeza — Mon, 03 Jul 2017 00:17:28 GMT

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

The macro above may be helpful.

Re: How to create macro variable list?

art297 — Mon, 03 Jul 2017 01:11:44 GMT

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

Re: How to create macro variable list?

Kurt_Bremser — Mon, 03 Jul 2017 06:25:40 GMT

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.

Re: How to create macro variable list?

ybz12003 — Wed, 05 Jul 2017 15:24:05 GMT

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.

Re: How to create macro variable list?

Reeza — Wed, 05 Jul 2017 17:18:47 GMT

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

Re: How to create macro variable list?

ybz12003 — Wed, 05 Jul 2017 17:39:26 GMT

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.

Re: How to create macro variable list?

Tom — Wed, 05 Jul 2017 17:46:14 GMT

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;

Re: How to create macro variable list?

Reeza — Wed, 05 Jul 2017 18:35:28 GMT

@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;