BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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 col:);
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.
ChrisNZ
Tourmaline | Level 20
Scott's answer is spot on, but you might want to look at the VVALUEX function too.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
VVALUEX? I don't follow. Can you demonstrate code related to the post?

Thanks.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
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!
andreas_lds
Jade | Level 19
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]
DanielSantos
Barite | Level 11
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
ChrisNZ
Tourmaline | Level 20
Cheers, Daniel 🙂



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.

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
  • 2077 views
  • 0 likes
  • 5 in conversation