DATA ORGANIZATION

[meiryem]

HELLOW I HAVE A DATA WITH 150 VARIABLES: A1-A50 C1-C50 E1-E50 AND I WANT TO CREATE A NEW DATA THAT LOOK LIKE THAT: A1 C1 E1 A2 C2 E2......A50 C50 E50 THANKS FOR YOUR HELP MEIR

[EyalGonen]

Hi @meiryem,

Can you explain why  the order of the variables matters to you, i.e. why do you want the variables in a specific order? In general the variables' order does not matter for processing the dataset.

[meiryem]

only for visual reason to show to my maneger

[PaigeMiller]

---

@meiryem wrote:

only for visual reason to show to my maneger

---

If you need a REPORT or an EXCEL file, you can use PROC REPORT or PROC TABULATE or PROC PRINT to change the order of the variables in the output. You do NOT need to re-arrange the columns in the DATA set.

[Tom]

---

@meiryem wrote:

only for visual reason to show to my maneger

---

So perhaps just make a list of the variables in the order you want and use that list to make the report.

If you know the prefixes then just generate the list of names.  For example write them to the SAS log.

data _null_:
  do i=1 to 50;
    put 'A' i ' B' i ' C' i;
  end;
run;

Then copy and paste them into your report code:

proc print data=have;
  var id <paste list here>
  ;
run;

[Kurt_Bremser]

Why do you have such numbered variables in the first place?

Transpose to a long dataset, extract the sequence number and basename from the _NAME_ variable, and you can then set up any order and combination/order you like, e.g. in PROC REPORT.

[meiryem]

thank you very much

[Hagay]

Hello,

You can try this code:

* Create sample data;
data UNORDERED(drop=i j);
	length 
		A1-A50 
		C1-C50
		E1-E50	8;

	array cols {*} A1-A50 C1-C50 E1-E50;
	do i=1 to 1000;
		do j=1 to 150;
			cols[j]=rand('UNIFORM');
		end;
		output;
	end;
run;

* Using the COLUMNS dictionary table to get all the columns and sort them as needed;
proc sql noprint;
	create table COLUMNS as
	select 
		name,
		substr(name,1,1) as Prefix length=1,
		input(substr(name, 2), 2.) as Suffix
	from 
		DICTIONARY.COLUMNS 
	where 
		libname='WORK' and memname='UNORDERED'
	order by 
		3,2;
quit;

* Generating proc sql code to re-arrange the columns;
filename sascode temp;

data _null_;
	set COLUMNS end=last;
	file sascode;
	if _n_=1 then do;
		put 'proc sql noprint;';
		put 'create table ORDERED as ';
		put 'select ';
	end;
	put name @;
	if ^last then put ',';
	if last then do;
		put 'from UNORDERED;';
		put 'quit;';
	end;
run;
%include sascode;

filename sascode clear;

Thanks,

Hagay