How to create macro variable list?

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

How to create macro variable list?

[ Edited ]

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;


Accepted Solutions
Solution
‎07-05-2017 03:35 PM
Super User
Super User
Posts: 7,060

Re: How to create macro variable list?

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;

View solution in original post


All Replies
Super User
Posts: 19,822

Re: How to create macro variable list?

PROC Star
Posts: 7,477

Re: How to create macro variable list?

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

 

Super User
Posts: 7,809

Re: How to create macro variable list?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 319

Re: How to create macro variable list?

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.

Super User
Posts: 19,822

Re: How to create macro variable list?

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

 

 

Super Contributor
Posts: 319

Re: How to create macro variable list?

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.

Solution
‎07-05-2017 03:35 PM
Super User
Super User
Posts: 7,060

Re: How to create macro variable list?

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;
Super User
Posts: 19,822

Re: How to create macro variable list?


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;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 200 views
  • 1 like
  • 5 in conversation