DATA Step, Macro, Functions and more

How to put dynamic columns?

Reply
N/A
Posts: 0

How to put dynamic columns?

Hi;

I have a dataset generated by proc transpose,so the columns can be like:COL1、COL2、.....COLN,and the number can be chaneged every time.
Now I want to put every column in a loop:
data _null_;
set dataset;
do i=1 to n by 1;
col_name=cats("col",i);
put ....; /*here,how to use the string value of "col_name" as a column name?*/
end;
run;

thanks!
Super Contributor
Super Contributor
Posts: 3,174

Re: How to put dynamic columns?

The DATA step technique using "of" works here as demonstrated in the SAS code below:

data _null_;
length all_cols $1000;
retain col1-col5 'x.';
all_cols = cats(of colSmiley Happy;
putlog _all_;
run;

Otherwise you could setup an ARRAY and only list the SAS variable prefix in the variable list, and have a DO/END code snippet that uses the DIM( function.

Scott Barry
SBBWorks, Inc.
PROC Star
Posts: 1,564

Re: How to put dynamic columns?

Scott's answer is spot on, but you might want to look at the VVALUEX function too.
Super Contributor
Super Contributor
Posts: 3,174

Re: How to put dynamic columns?

VVALUEX? I don't follow. Can you demonstrate code related to the post?

Thanks.

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: How to put dynamic columns?

Thanks for your reply,I've got your mean.But my intention is to put every column separately so I used a loop.My problem is how to put a column who's name is a value of another variable that is not determinate.Thanks!
Super Contributor
Posts: 264

Re: How to put dynamic columns?

You can use proc sql and sashelp.vcolumn to get the number of transposed columns, in fact you get the number of columns starting with 'Col'. The following code creates a global macro-variable maxColumns, afterwards the variable is used to create an array statement. The code is untested.

[pre]proc sql noprint;
select count(*)
into :maxColumns
from sashelp.vcolumn
where lowcase(libname) = 'your_lib' and
lowcase(memname) = 'your_dataset' and
name like 'Col%'
;
quit;[/pre]

[pre]data _null_;
set dataset;
array cols{&maxColumns} col1-col&maxColumns;
do i=1 to &maxColumns by 1;
put cols{i};
end;
run;[/pre]
Super Contributor
Posts: 474

Re: How to put dynamic columns?

Chris suggestions would also be a good alternative.

If I may...

You could assign the formatted value to a another temporary var for displaying purpose.

Just put the following inside the loop (after the col_name=...):

COL_VALUE=VVALUEX(COL_NAME); /* assigns formatted "COL_NAME" var value */
put COL_VALUE;

And you'll get the separated put.

Check here for documentation:
http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a002233818.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
Super Contributor
Super Contributor
Posts: 3,174

Re: How to put dynamic columns?

The original post demonstrated a single concatenated string rather than individual PUT variables.

Embellishing on prior posts and presuming a column/variable prefix is known, the ARRAY statement can specify a generic prefix with a colon character - shown below:


data _null_;
set dataset;
* all COL-prefix vars in WORK.DATASET included here. ;
array a_cols (*) col: ;
do i=1 to dim(a_cols);
* assign a formatted value as well to a temp-named var. ;
temp_var = vvalue(a_cols(i));
put a_cols(i)= temp_var=;
end;
run;


Scott Barry
SBBWorks, Inc.
PROC Star
Posts: 1,564

Re: How to put dynamic columns?

Cheers, Daniel Smiley Happy



The intend of the post [pre] do i=1 to n by 1;
col_name=cats("col",i);
put ....; /*here,how to use the string value of "col_name" as a column name?*/
end;
[/pre]was obviously to put= the variables one by one, not to build a long string of all the names.



I reckon Scott's use of the colon
[pre] array a_cols (*) col: ; [/pre]is the most elegant way to deal transparently with the unknown number of columns.


After that, 3 ways to output the values are all good:
[pre] temp_var = vvaluex('col'||put(i,3. -l)); * we don't access the array here, but the variable directly;
temp_var1 = vvalue(a_cols(i));
put a_cols(i)=
temp_var =
temp_var1=;[/pre]

I mentioned vvaluex() as an alternative because it is very useful, and too little known. Since we have an array in this case, it is no better than referencing the array directly (it is actually worse as there is an extra step to create a temporary variable), but it can access any variable name (not arrays only), and can replace macro code in some cases.

Ask a Question
Discussion stats
  • 8 replies
  • 212 views
  • 0 likes
  • 5 in conversation