BookmarkSubscribeRSS Feed
Jumboshrimps
Quartz | Level 8

Have an output table from a proc transpose with about 1000 records, 20 variables.

Data contained is a "1" or "." (null).

 

Would like to concatenate all variable names with a "1' value into a new, separate field, (building an array of column names and using catx, I suppose), on each row so I have a list in this new, separate field (in the same row, separated by a ",") of "field1, field7, field10, field 14", etc.   - IF  those fields on that row have a "1" value.  Of the twenty variables, the most I've seen is  five with a "1" value, so it's not a long string.  90% of the rows have only one occurrence that is not null.  

 

This is similar to the Excel index/match function which returns the column header in a matrix of data (not the cell address such as A, B,, AB, AC, AD, etc.), if that helps. 

 

Thanx.

5 REPLIES 5
PaigeMiller
Diamond | Level 26

I have a suspicion that there are better ways to arrange this output than "field1,field4,field8,field10". Please tell us what you plan to do next once you have this new field created. What analysis or chart or table are you going to produce next? Help us help you, let us know where you plan to go once you have this new field created, we may have better suggestions than "field1,field4,field8,field10". (Letting us know what you are going to do next is always a good thing to do in every new problem you post, and it is something I have asked you to provide at least once before).

--
Paige Miller
Jumboshrimps
Quartz | Level 8

I am giving it to my supervisor - because he asked me to..

There is no "next step".

quickbluefish
Quartz | Level 8

I agree with @PaigeMiller that there's probably a better approach to this, but if that's what you want, you can achieve this like this:


data want;
set have;
array f {*} fieldvars:;
length fields $1000 ;
fields="";
do i=1 to dim(f);
  if f[i] then fields=catx(',',fields,vname(f[i]));
end;
run;
quickbluefish
Quartz | Level 8
...if you want each of them to be in quotes, you can just wrap the vname(...) in the quote() function, i.e.,
quote(vname(f[i]))
Jumboshrimps
Quartz | Level 8

This is great.

Part of what I used before on a much smaller list of variables:

 

DATA BASE;

Length TESTS $3000;
SET DATA1;
TESTS=catx(',',of APPLES--ZUCHINI);

 

So I don't need to assign the range of variables specifically? 

"array f {*} fieldvars:;"   will grab all variables, without declaring the array with the name of var1, and finishing the array with the name of the last var?  Would work on any data set, (ir)regardless of variable names?

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 479 views
  • 0 likes
  • 3 in conversation