Dynamic Retain of vars

[Ronein]

Hello

I want to create a new data set called "want" based on data set called "have".

I want to keep variable ID and also keep all variables start with _ (It was done as you can see in the code).

Then I want to retain the variables in the following way:

First variable will be "ID"

Then the order of variables will be by the number appears in the third argument of the variable name .

So the expected order will be : ID  _X1      _X2      _X3       _X4     _X5

In the future the "have" data set can contain more variables (for example also _X6 ,_X7 and so on)

I am looking for a dynamic way to create the required retain please

Data have;
input ID X1 X2 X3 _X2 Y W _X1 Z _X3 _X5 _X4 Q;
cards;
999 1 2 3 4 5 6 7 8 9 10 11 12
777 2 3 4 5 6 7 8 9 10 11 12 13
;
Run;

Data want;
set have(keep=ID _X: );
Run;

[Kurt_Bremser]

I see no RETAIN statement here. Are you actually talking about the KEEP statement or KEEP= option?

Why is the order of variables important for you? Variables are identified by name, not by position.

[Ronein]

Hello,

I want that the retain statement will be written automatically without typing the variables order manually.

Data have;
input ID X1 X2 X3 _X2 Y W _X1 Z _X3 _X5 _X4 Q;
cards;
999 1 2 3 4 5 6 7 8 9 10 11 12
777 2 3 4 5 6 7 8 9 10 11 12 13
;
Run;

Data want;
Retain ID _X1 _X2 _X3 _X4 _X5;/**I want that it will be done automatically without typing the order of variables manually here**/
set have(keep=ID _X: );
Run;

[Kurt_Bremser]

Do the variables always follow the same pattern ("ID" followed by those starting with an underline)?

And once again I ask: why is the order important?

[Ronein]

Yes,

always keep variables start with _  and also variable ID.

always display the variables in the order of ID  and then the _ variables by their number appear in 3rd digit(always).

Order of variables is important because it represents order of overide rules  that apply in process.

So need to see them by their logic order 

[Kurt_Bremser]

Which "process"? Please show the SAS statement(s) you use where order of variables in the dataset is important.

[Ronein]

Data have;
input ID X1 X2 X3 _X2 Y W _X1 Z _X3 _X5 _X4 Q;
cards;
999 1 2 3 4 5 6 7 8 9 10 11 12
777 2 3 4 5 6 7 8 9 10 11 12 13
;
Run;

Data want;
Retain ID _X1 _X2 _X3 _X4 _X5;
/**I want that it will be done automatically without typing the order of variables manually here**/
set have(keep=ID _X: );
Run;

[Kurt_Bremser]

You still refuse to answer my question. Where will dataset want be used where the order of variables is necessary to make SAS code work correctly?

[Ronein]

I need to send report and in this report I am required to show the variables in the required order .

The reason is that there is a production process flow and the engineers need to see the variables by the logic order.

In the future there might be added new variables and then we want to create a dynamic process that retain the varaibles automatically by the logic that I wrote before (instead of typing the vars manually) 

[Kurt_Bremser]

Now we are getting somewhere. "Report" is the most important fact here.

From your data, this can be achieved fully automatic by transposing and using the power of PROC REPORT:

data have;
input ID X1 X2 X3 _X2 Y W _X1 Z _X3 _X5 _X4 Q;
cards;
999 1 2 3 4 5 6 7 8 9 10 11 12
777 2 3 4 5 6 7 8 9 10 11 12 13
;

proc transpose data=have out=long;
by id notsorted;
var _:;
run;

proc report data=long;
column id col1,_name_;
define id / group;
define col1 / "" analysis;
define _name_ / "" across;
run;

Result:

ID	_X1	_X2	_X3	_X4	_X5
777	8	5	10	12	11
999	7	4	9	11	10

You just need to use ODS to send the output to the wanted file format.

If you intend to just create a CSV, do this:

proc sql noprint;
select name into :names separated by ","
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and name like '^_%' escape "^"
order by name;
select name into :put separated by " "
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and name like '^_%' escape "^"
order by name;
quit;

data _null_;
set have;
file "~/ronein.csv" dlm="," dsd;
if _n_ = 1 then put "id,&names.";
put id &put.;
run;

[PaigeMiller]

PROC REPORT can put variables in a desired order. Your data set does not have to be in that same order. Requiring data set to be in a certain order for this problem is a logical error that causes you to take unnecessary actions.

This is a common issue here in these forums, and not just with you @Ronein . People get so intensely focused on a particular way of coding something that they lose sight of the big picture and they don't inform us of the big picture. In this example, we had to DRAG the information about the big picture out of you. This is not a good way to do things. You will get the help you want more quickly if you answer our questions. You should always, in the first post, explain the big picture, in this case that you want a report with columns in a particular order.

By the way, this is so common, it has a name: the XY Problem. See also: https://en.wikipedia.org/wiki/XY_problem

[andreas_lds]

"keep" and "keep=" don't change the order of variables.

[Reeza]

Assuming you want to create a new data set with the ID variables and _X* variables I would do that. RETAIN seems irrelevant to me.

Data have;
input ID X1 X2 X3 _X2 Y W _X1 Z _X3 _X5 _X4 Q;
cards;
999 1 2 3 4 5 6 7 8 9 10 11 12
777 2 3 4 5 6 7 8 9 10 11 12 13
;
Run;

proc sql;
select name into :var_list separated by ", "
from sashelp.vcolumn 
where libname='WORK' 
and memname = 'HAVE' 
and (name = 'ID' or name like '_X%')
order by name;
quit;

proc sql;
create table want as
select &var_list
from have;
quit;

---

@Ronein wrote:

Hello

I want to create a new data set called "want" based on data set called "have".

I want to keep variable ID and also keep all variables start with _ (It was done as you can see in the code).

Then I want to retain the variables in the following way:

First variable will be "ID"

Then the order of variables will be by the number appears in the third argument of the variable name .

So the expected order will be : ID  _X1      _X2      _X3       _X4     _X5

In the future the "have" data set can contain more variables (for example also _X6 ,_X7 and so on)

I am looking for a dynamic way to create the required retain please

Data have;
input ID X1 X2 X3 _X2 Y W _X1 Z _X3 _X5 _X4 Q;
cards;
999 1 2 3 4 5 6 7 8 9 10 11 12
777 2 3 4 5 6 7 8 9 10 11 12 13
;
Run;

Data want;
set have(keep=ID _X: );
Run;

---

[Tom]

Ignoring the side issue (pet peeve) about whether there is any value in creating datasets that have variables in an order that make it easier for HUMANS to work with them (you can tell which side that silly argument I am on) ...

Your question is how to get a list of existing variables from a dataset based on some criteria.  It is normally simplest to use PROC SQL to create a macro variable with the list of variables in the order you need.  (Note there is a limit of 64K bytes for a macro variable so if the list is extremely long use a different method of generating the code from the ordered list.)

You can use DICTIONARY.COLUMNS or PROC CONTENTS to see which variables exist in a dataset.

proc sql noprint;
select nliteral(name)
  into :varlist separated by ' '
  from dictionary.columns
  where libname='WORK'
    and memname='HAVE'
    and substr(upcase(name),1,2)='_X'
  order by upcase(name)
;
quit;

If the variables names are like _X1, _X2 .... _X9, _X10, _X11 ...  then change the order by clause.

  order by input(substr(name,3),32.)

Now use the list in the SAS code you need to make your report:

proc print data=have;
  var id &varlist;
run;

Or your dataset:

data want;
  retain id &varlist;
  set have(keep=id &varlist);
run;