BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
art297
Opal | Level 21

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

 

Kurt_Bremser
Super User

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.

ybz12003
Rhodochrosite | Level 12

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.

Reeza
Super User

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

 

 

ybz12003
Rhodochrosite | Level 12

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.

Tom
Super User Tom
Super User

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;
Reeza
Super User

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

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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