topic Re: Creating a new Variable from 60 in SAS Programming

Creating a new Variable from 60

Mscarboncopy — Thu, 27 May 2021 13:55:57 GMT

I have 60 variables (selected = 1, not selected = 0 VAR1 to VAR60) that I wanted to collapse into one variable with 60 keys (MAINVAR).

However, each one of my ids, could have more than 1 selected.

If I did a simple if then, it overwrites.

What is the most effective way of doing this?

It would then become a stacked file. Each id that has more than one variable selected would have multiple entries.

To make it a little bit more complex, for each selected there is a corresponding date var and also corresponding timing variable (past present selected not selected). However, I could do the same thing for the timing and the date variables, once I know how to do this (I think).

Example of what the datafile looks like now (leaving dates and timing out):

ID VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 etc…

A 1 0 1 1 0 0
B 0 1 0 0 0 0

C 1 1 1 1 0 0

D 0 0 0 1 1 1

Example of how it would be from the example above:

ID MAINVAR (keys 1-60)

A 1

A 3

A 4

B 2

C 1

C 2

C 3

C 4

D 4

D 5

D 6

Thank you!

Re: Creating a new Variable from 60

PaigeMiller — Thu, 27 May 2021 14:18:05 GMT

data have;
Input id $ VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 ;
cards;
A     1          0         1         1         0        0
B     0          1         0         0         0        0
C     1          1         1         1         0        0
D     0          0         0         1          1       1
;

data want;
    set have;
    array v var1-var6;
    do mainvar=1 to dim(v);
        if v(mainvar)=1 then output;
    end;
    keep id mainvar;
run;

Re: Creating a new Variable from 60

mkeintz — Thu, 27 May 2021 14:20:43 GMT

This has DATA step with a do loop written all over it:

You need to

iterate (using a do loop) over all the VARs (use an array)
If the array element is 1 then
1. assign the element-number within the array to MAINVAR
2. output the result

data want (keep=id mainvar);
  set have;
  array _var {*} var: ;
  do mainvar=1 to dim(_var);
    if _var{mainvar}>0 then output;
  end;
run;

This relies entirely on the assumption that variable names range from VAR1 through VARxx, (xx=60 in your example) with no missing names in that list.

Re: Creating a new Variable from 60

PaigeMiller — Thu, 27 May 2021 14:56:26 GMT

@mkeintz wrote:

This has DATA step with a do loop written all over it:

You need to

iterate (using a do loop) over all the VARs (use an array)

If the array element is 1 then

assign the element-number within the array to MAINVAR

output the result
data want (keep=id mainvar);
  set have;
  array _var {*} var: ;
  do mainvar=1 to dim(_var);
    if _var{mainvar}>0 then output;
  end;
run;
This relies entirely on the assumption that variable names range from VAR1 through VARxx, (xx=60 in your example) with no missing names in that list.

I don't think missing variable names in the VAR1 through VARxx is a problem in your code. I think actually it would be a problem in my code, but that's easily fixed.

Re: Creating a new Variable from 60

Reeza — Thu, 27 May 2021 14:59:21 GMT

Here's some references you may find useful.

Transposing data tutorials:

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

Re: Creating a new Variable from 60

mkeintz — Thu, 27 May 2021 15:50:57 GMT

@PaigeMiller wrote:
@mkeintz wrote:

This has DATA step with a do loop written all over it:

You need to

iterate (using a do loop) over all the VARs (use an array)

If the array element is 1 then

assign the element-number within the array to MAINVAR

output the result
data want (keep=id mainvar);
  set have;
  array _var {*} var: ;
  do mainvar=1 to dim(_var);
    if _var{mainvar}>0 then output;
  end;
run;
This relies entirely on the assumption that variable names range from VAR1 through VARxx, (xx=60 in your example) with no missing names in that list.
I don't think missing variable names in the VAR1 through VARxx is a problem in your code. I think actually it would be a problem in my code, but that's easily fixed.

What I was trying to warn against was starting out with, say VAR1 VAR2 VAR5 VAR6. They would form a 4-element array, with MAINVAR=3 for VAR5, and MAINVAR=4 for VAR6. Of course, I could avoid that problem by changing

array _var {*} var: ;

array _var {*} var1-var60 ;

which is what I should have done to begin with.

Re: Creating a new Variable from 60

Mscarboncopy — Thu, 27 May 2021 18:01:42 GMT

This worked really well. Thank you.

Is there a way to do this for data variables? The main variable in this case would be dates instead of 1 selected.

Re: Creating a new Variable from 60

Mscarboncopy — Thu, 27 May 2021 18:02:36 GMT

I don't have missing.

Re: Creating a new Variable from 60

PaigeMiller — Thu, 27 May 2021 18:15:31 GMT

@Mscarboncopy wrote:

This worked really well. Thank you.

Is there a way to do this for data variables? The main variable in this case would be dates instead of 1 selected.

I think this needs clarification and an example.