topic Re: DATA ORGANIZATION in SAS Users Group in Israel

DATA ORGANIZATION

meiryem — Tue, 23 Jul 2024 08:52:41 GMT

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

Re: DATA ORGANIZATION

EyalGonen — Tue, 23 Jul 2024 09:00:31 GMT

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.

Re: DATA ORGANIZATION

Kurt_Bremser — Thu, 25 Jul 2024 05:48:42 GMT

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.

Re: DATA ORGANIZATION

meiryem — Tue, 23 Jul 2024 15:04:14 GMT

thank you very much

Re: DATA ORGANIZATION

meiryem — Tue, 23 Jul 2024 15:06:59 GMT

only for visual reason to show to my maneger

Re: DATA ORGANIZATION

PaigeMiller — Tue, 23 Jul 2024 15:24:58 GMT

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

Re: DATA ORGANIZATION

Tom — Tue, 23 Jul 2024 17:01:23 GMT

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

Re: DATA ORGANIZATION

Hagay — Tue, 23 Jul 2024 17:58:36 GMT

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