topic Re: How to combine multiple observations into one single observation without having the group var ke in SAS Programming

How to combine multiple observations into one single observation without having the group var key.

veeresh10 — Mon, 07 Sep 2020 11:47:05 GMT

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.

Re: How to combine multiple observations into one single observation without having the group var ke

PeterClemmensen — Mon, 07 Sep 2020 11:48:38 GMT

What should that single observation contain? Show us some representable sample of your data please.

Re: How to combine multiple observations into one single observation without having the group var ke

veeresh10 — Mon, 07 Sep 2020 11:59:54 GMT

All variables are character variables and contain data with a max length of 50.

Re: How to combine multiple observations into one single observation without having the group var ke

Shmuel — Mon, 07 Sep 2020 12:42:25 GMT

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;

Re: How to combine multiple observations into one single observation without having the group var ke

veeresh10 — Mon, 07 Sep 2020 13:19:05 GMT

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.

Re: How to combine multiple observations into one single observation without having the group var ke

Shmuel — Mon, 07 Sep 2020 13:34:44 GMT

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;