topic Re: dynamic columns in SAS Programming

dynamic columns

Ody — Tue, 25 Jul 2023 19:49:56 GMT

Hello,

I'm trying to parse a column called LINE that is delimited by spaces(' ').

Example records:

somedata1 somedata2

somedata1 somedata2 somedata3

somedata1

somedate1 somedate2 somedate3 somedate4

The number of delimiters is unknown. To handle this I'm taking the max count of spaces:

proc sql noprint;
  select max(countc(LINE,' ')) + 1 into: nums
  from testdata;
quit;

I would like to create additional columns with a prefix of col_ for the max number of delimiters found in the LINE column. So far I have the following code but it's not quite working.

data test;
set mydata;

  %macro cols;
    %do i = 1 %to &nums;
      col_&i = scan(LINE, &nums, ' ');
    %end;

  %mend cols;

  %cols;
run;

I would expect the Test dataset to contain 4 additional columns called Col_1, Col_2, Col_3, and Col_4; each containing the delimited values from LINE.

Put another way:

Col_1 will contain the values equal to somedata1

Col_2 will contain the values equal to somedata2

Col_3 will contain the values equal to somedata3

Col_4 will contain the values equal to somedata4

The results are getting close to the requirements but I'm missing something; its not quite right. I havent coded in SAS in a few years beyond simple CRUD stuff and am quite rusty.

Appreciate any help with this, thanks!

Re: dynamic columns

Kurt_Bremser — Tue, 25 Jul 2023 20:02:54 GMT

Try this:

data long;
set have;
n = _n_;
do i = 1 to countw(line);
  col = scan(line,i);
  output;
end;
keep n col;
run;

proc transpose data=long out=want (drop=_name_);
by n;
var col;
run;

Re: dynamic columns

Reeza — Tue, 25 Jul 2023 20:06:36 GMT

Do you have other columns you need to account for?

Re: dynamic columns

Ody — Tue, 25 Jul 2023 20:28:34 GMT

I do not

Re: dynamic columns

Astounding — Tue, 25 Jul 2023 22:27:16 GMT

Your SCAN function refers to &nums when it should be using &i.

Re: dynamic columns

Tom — Tue, 25 Jul 2023 23:39:11 GMT

No need to write a macro. Just use an array.

You can use the macro variable to set the dimension of the array. Instead of counting spaces count the actual number of terms.

proc sql noprint;
  select max(countw(LINE,' ')) into :nums trimmed
  from testdata;
quit;

Now use the macro variable to define the number of variables to create. Make sure to tell SAS how LONG to make the variables.

data test;
  set testdata;
  array col_ [ &nums ] $50 ;
  do i=1 to countw(line,' ');
    col_[i] = scan(line,i,' ');
  end;
run;

Re: dynamic columns

Ody — Wed, 26 Jul 2023 02:22:53 GMT

This is exactly what i was looking for!

I guess I had the right pieces/idea, even have a few tries using arrays but couldn't get it to work. I was struggling (for more time than i care to admit) on how to execute it.

Thank you and to everyone else that offered some insight!