topic Re: Need help to create a bundle of new variables from the old variables in SAS Programming

Need help to create a bundle of new variables from the old variables

QiaoMaggieZhang — Mon, 29 Apr 2024 18:45:55 GMT

Hi everyone,

I am working on a dataset that contains both old and new bundles of variables. I need to create a new list of variables from these two lists and obtain all the unique values for each row.

Here is an example of what I have:

Subject old_var1 old_var2 old_var3 old_var4 ... new_var1 new_var2 new_var3 new_var4 ...

1 A C B . C D . .

2 X Z . . . . . .

3 C D X . X C D .

Here is what I need to create:

Subject combine_var1 combine_var2 combine_var3 combine_var4 ....

1 A C B D

2 X Z . .

3 C D X .

Please help

Thank you !!

Re: Need help to create a bundle of new variables from the old variables

Astounding — Wed, 01 May 2024 16:27:22 GMT

Maybe I'm overlooking something. It seems like you just need to rename some variables. Is there more than that to your question?

Re: Need help to create a bundle of new variables from the old variables

Reeza — Wed, 01 May 2024 16:55:57 GMT

Transpose your data to a long format to make it more usable
Select distinct values
Transpose back to a wide format, for desired results (not recommended)

data have;
    input Subject old_var1 $ old_var2 $ old_var3 $  old_var4 $  new_var1 $ new_var2 $ new_var3 $  new_var4 $;
    cards;
1                  A               C                B                 .                     C                D               .                     .                  
2                 X                Z                 .                  .                     .                  .                .                     .                    
3                 C                D               X                 .                     X                C               D                   .  
;
run;

proc transpose data=have out=long (rename=col1=value);
    by subject;
    var old_var1--new_var4;
run;

proc sql;
    create table long_want as select distinct subject, value from long where not 
        missing(Value);
quit;

proc transpose data=long_want out=want(drop=_name_) prefix=combine_var;
    by subject;
    var value;
run;

@QiaoMaggieZhang wrote:

Hi everyone,

I am working on a dataset that contains both old and new bundles of variables. I need to create a new list of variables from these two lists and obtain all the unique values for each row.

Here is an example of what I have:

Subject old_var1 old_var2 old_var3 old_var4 ... new_var1 new_var2 new_var3  new_var4 ...

1 A C B . C D . .

2 X Z . . . . . .

3 C D X . X C D .

Here is what I need to create:

Subject combine_var1 combine_var2 combine_var3   combine_var4 ....

1 A C B D

2 X Z . .

3 C D X .

Please help

Thank you !!

Re: Need help to create a bundle of new variables from the old variables

Tom — Wed, 01 May 2024 20:30:58 GMT

Take advantage of using the IN operator with an array name to simplify the direct approach.

data have;
  input (Subject old_var1-old_var4 new_var1-new_var4) ($);
cards;
1 A C B . C D . .
2 X Z . . . . . .
3 C D X . X C D .
;

data want;
  set have;
  array old old_var: new_var: ;
  array combine[8] $8 ;
  do i=1 to dim(old);
    if not missing(old[i]) then do;
      if old[i] not in combine then do;
        j=sum(j,1);
        combine[j]=old[i];
      end;
    end;
  end;
  drop i j;
run;

Result