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.
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).
I am giving it to my supervisor - because he asked me to..
There is no "next step".
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;
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?
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!
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.
Ready to level-up your skills? Choose your own adventure.