Need to combine more than 10 obs data into one observation.
problem is we not have any key to hold as group var.
I have more then
24 var s
a b c d e f g t h i ...... aa ab ac ad
need to combine multiple observations into one observation.
ex- dataset contains 20 or more variables and 10 to 15 observations.
so all observations combine it into one single observation.
What should that single observation contain? Show us some representable sample of your data please.
All variables are character variables and contain data with a max length of 50.
If you want all text in one long variable compressed to single space between words in the order of the input lines than try next code:
libname mytxt xlsx "<path and name of file>.xlsx";
data have;
set mytxt.<sheet_name>;
run;
%let cols = 50; /* adapt to max columns read */
data want(keep=wanted);
length wanted $10000; /* adapt to max length needed */
retain wanted ;
set have end=eof;
array all {*} $ _ALL_;
do i=1 to dim(all);
if not missing(all(i)) then
all(i) = compbl(all(i));
wanted = cats(wanted, all(i));
end;
if eof then output;
run;
your code working great with the concept of hold values into one final variable. But my requirement is to club obs into one obs and bring all obs values into one obs value with space with respect to the columns.
I have attached a sheet with the expected result Kindly help on this.
I see, you want to concatenate all text of the same column, so finally the output will contain all the variables as in the input:
%let cols = 50; /* adapt to max columns read */
data want;
set have end=eof;
length txtout1-txtou&cols $80; /* adapt to max length needed per column */
array colin {*} $ col1-col&cols;
array txtout {*} $ txtout1-txtou&cols;
do i=1 to &cols;
txtout(i) = cats(txtout(i), colin(i));
end;
if eof then output;
keep txtout1-txtou&cols ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.