topic text to columns in SAS in SAS Programming

text to columns in SAS

smh92 — Mon, 07 Aug 2023 20:44:38 GMT

Hi, I want to process a comma-separated list of values in a variable (e.g., Q8) and create corresponding response variables (Q8_1, Q8_2, ..., Q8_17). For each value in the list, I want to set the corresponding response variable to 1 if there is a value. If the value is missing, the corresponding response variable should remain blank. Here is an example of what the variable initially looks like:

15,17

11,12,13,14,15

1,2,3,4,5,6,7,8,9,10

I tried something like this, but it's not accounting correctly for values 10+. any suggestions? I am not married to this code, so if someone has a completely different one, I am okay with that.

data numeric2;

set numeric;

run;

%macro initial2(var, num);

data numeric2;

format &var._1 - &var._&num 8.;

set numeric2;

run;

%mend;

%macro pop_var(var, num);

data numeric2;

set numeric2;

if index(&var, "&num")>0 then &var._&num = 1;

run;

%mend pop_var;

%macro run_pop(var2, num2);

%initial2(&var2, &num2);

%do i = 1 %to &num2;

%pop_var(&var2, &i);

%end;

%mend;

%run_pop(Q8, 17);

Re: text to columns in SAS

PaigeMiller — Mon, 07 Aug 2023 21:40:20 GMT

I think an ARRAY is a much better choice here at working with many columns in a data set, than macros.

data want;
    set have;
    array q8 q8_1-q8_17;
    do i=1 to countw(string,',');
    	do j=1 to 17;
    	    if scan(string,i,',')=j then q8(j)=1;
	    end;
    end;
    drop i j;
run;

This sets the variables to 1 if that value is found, and missing if it is not found.

Re: text to columns in SAS

smh92 — Mon, 07 Aug 2023 21:54:15 GMT

Thanks so much! Do you know why it's only creating the variables q8_1-q8_17 but not populating 1 or blank?

Re: text to columns in SAS

ballardw — Mon, 07 Aug 2023 22:36:33 GMT

@smh92 wrote:

Thanks so much! Do you know why it's only creating the variables q8_1-q8_17 but not populating 1 or blank?

You would have to provide data in the form of working data step AND the code that you actually ran to actually answer that.

Possibly include the log with the code and any messages might provide a clue but data is the key.

My approach to YOUR requirement:

data example;
   length q8 $ 55;
   input q8 $;
datalines;
11
.
.
.
15,17
.
11
11,12,13,14,15
1,2,3,4,5,6,7,8,9,10
;

data want;
   set example;
   array q(*) q8_1 - Q8_17;
   if not missing(q8) then do i=1 to countw(q8);
      index= input(strip(scan(q8,i)),5.);
      q[index]=1;
   end;
run;

Re: text to columns in SAS

smh92 — Mon, 07 Aug 2023 23:01:02 GMT

This works. Thanks again for your help!