topic Re: creating a new variable and changing wide to long format in SAS Programming

creating a new variable and changing wide to long format

Mscarboncopy — Mon, 25 Jan 2021 19:18:42 GMT

Hi I am trying to find out what is the best way to create a variable that I need and change the file format at the same time. My file looks like this (0 is not selected, 1 is selected). I need to create a variable called med that would have values 0 to 8 (depending on what was answered for med_1 to med_8). My issue is that if I use an array it will overwrite, as any ID (obs) can have multiple combinations of selected options (they can select all options or none and any combination in between). I could add all the possible combinations, however, my data file has other variables with 1-50 answer options that can be all selected, and I was hoping to do the same for those. I will not need the non selected (0). My goal is to have a final long data file that only contains the observations that selected at least one of the options. I have looked at wide to long articles and answers here, but I can't seem to adapt the information to what I need. Thank you.

OBS	med___1	med___2	med___3	med___4	med___5	med___6	med___7	med___8
1	0	0	0	0	0	0	0	1
2	1	0	1	0	0	0	0	0
3	1	0	0	0	0	0	0	0
4	1	1	1	0	0	0	0	0
5	0	0	0	0	0	0	0	0
6	1	1	1	1	1	0	0	0

Re: creating a new variable and changing wide to long format

PeterClemmensen — Mon, 25 Jan 2021 19:26:26 GMT

So what does your desired long data look like given this sample?

Re: creating a new variable and changing wide to long format

ballardw — Mon, 25 Jan 2021 20:07:50 GMT

Since you mention that you have a lot of other variables it would be a good idea to share how you expect the data to look including some of those others.

It may help to provide a worked example using a smaller set of values, say med_1 to med_4 and similar for at least one other variable if it is also a multiple response value like these Med values.

Re: creating a new variable and changing wide to long format

Mscarboncopy — Mon, 25 Jan 2021 20:24:27 GMT

5 is not there because it only had 0's nothing selected. it would look like this.

OBS	Med
1	8
2	1
2	3
3	1
4	1
4	2
4	3
6	1
6	2
6	3
6	4
6	5

Re: creating a new variable and changing wide to long format

Mscarboncopy — Mon, 25 Jan 2021 20:25:31 GMT

Yes, the others are also select not select, exactly like the med vars here. Thank you.

Re: creating a new variable and changing wide to long format

ballardw — Mon, 25 Jan 2021 20:35:17 GMT

@Mscarboncopy wrote:

Yes, the others are also select not select, exactly like the med vars here. Thank you.

You need to show what the output involving these other variables would be. It seems pretty obvious that there should be another another variable but how to generate it is not obvious because what if the Obs=1 has 3 values for the other variable? You currently have exactly 1 value for Med but what happens with the other variable involved? This can get to be a very complex issue depending on what you think you want.

Re: creating a new variable and changing wide to long format

Mscarboncopy — Mon, 25 Jan 2021 20:51:28 GMT

Oh, I see what you mean. I apologize.

So yes, it would have an issue right? Especially when I am adding variables that have 50+ options. So I added a var Living with 1-8 values just like Med. 0 for not selected, 1-8 to the selected ones.

OBS	Med	Living
1	8	2
1		3
2	1	1
2	3	2
2		3
3	1	0
4	1	1
4	2	2
4	3	3
4		5
6	1	1
6	2	2
6	3	0
6	4	0
6	5	0

Re: creating a new variable and changing wide to long format

Mscarboncopy — Tue, 26 Jan 2021 12:18:35 GMT

Also I can output the other variables that involve more than 50 selection options as their own file. This would allow me to have a smaller (separate) file with the variables that are like med and living(shown here). But I still need to know how to create the new vars, changing the format and how to handle the missing shown. Thank you.

Re: creating a new variable and changing wide to long format

Kurt_Bremser — Tue, 26 Jan 2021 12:56:22 GMT

Transpose first, and then transform the result:

data have;
input OBS med___1 med___2 med___3 med___4 med___5 med___6 med___7 med___8;
datalines;
1 0 0 0 0 0 0 0 1
2 1 0 1 0 0 0 0 0
3 1 0 0 0 0 0 0 0
4 1 1 1 0 0 0 0 0
5 0 0 0 0 0 0 0 0
6 1 1 1 1 1 0 0 0
;

proc transpose
  data=have
  out=long (
    where=(col1 ne 0)
  )
;
by obs;
var med:;
run;

data want;
set long;
med = scan(_name_,-1,"_");
drop _name_ col1;
run;

Re: creating a new variable and changing wide to long format

Mscarboncopy — Tue, 26 Jan 2021 16:41:19 GMT

This is it! Thank you so much.

Questions:

1. Can I add other variables to this? If I am adding other vars (A B C) would this be correct below.

2. what is the vat col2? My output is showing col2 and they are all missing except one obs has a 1.

proc transpose data=have out=long ( where=(col1 ne 0) ) ; by obs; var med: A: B: C:; run; data want; set long; med = scan(_name_,-1,"_"); A= scan(_name_,-1,"_"); B = scan(_name_,-1,"_"); C= scan(_name_,-1,"_"); drop _name_ col1; run;

Re: creating a new variable and changing wide to long format

Mscarboncopy — Tue, 26 Jan 2021 16:49:08 GMT

I see this does not work if I want to add more variables. It overwrites. This is the issue brought up before. So my option would be maybe proc transpose each variable and then merge the files ?

Re: creating a new variable and changing wide to long format

Mscarboncopy — Tue, 26 Jan 2021 17:10:22 GMT

When I transpose each variable and merge I then have the issue that the values of the first var will repeat to fill in blanks and the same will keep happening when I add more variables. I am trying to remember how we do this, to stop filling in blanks. First obs? I am not sure. I would use something like that in my merge statement, right? The way I have it now you can see the last ID has the same value repeating because it has 2 values for the second var. This is what others were telling me. And I believe it is okay to leave the others as missing. But I need to find out how to prevent the repetition to fill in blanks.

obs	med	living
1	7
2	4
3	8
4	2	2
5	2
6	3	1
6	3	8

Re: creating a new variable and changing wide to long format

Tom — Tue, 26 Jan 2021 17:39:30 GMT

To avoid the automatic RETAIN (the feature that makes one to many merges work right) you can use CALL MISSING().

data want;
   merge one two three;
   by id;
   output;
   call missing(of _all_);
run;

Re: creating a new variable and changing wide to long format

Mscarboncopy — Tue, 26 Jan 2021 17:52:46 GMT

That is perfect. Thank you Tom. Can I just ignore the var COL2 in my output? I am planning on dropping it, but just wanted to make sure I do not need to use it to check for issues.

Re: creating a new variable and changing wide to long format

Kurt_Bremser — Wed, 27 Jan 2021 08:15:08 GMT

When you get a COL2 variable, this means that the BY variable is not unique, there are two observations for at least one OBS in your original dataset.

For a transpose like this to work consistently, you need to make sure first that your BY variable is unique.

Then, you do a double transpose with some little preparation in between:

data have;
input id $ med_1 med_2 liv_1 liv_2;
datalines;
A 0 1 1 0
B 1 1 0 1
C 1 0 0 1
D 1 1 1 1
;

proc transpose
  data=have
  out=long1 (where=(col1 ne 0))
;
by id;
var med: liv:;
run;

data long2;
set long1;
seq = input(scan(_name_,2,"_"),best.);
_name_ = scan(_name_,1,"_");
run;

proc sort data=long2;
by id seq;
run;

proc transpose
  data=long2
  out=want (drop=seq _name_)
;
by id seq;
var seq;
id _name_;
run;

Re: creating a new variable and changing wide to long format

Mscarboncopy — Wed, 27 Jan 2021 12:50:41 GMT

Thank you so much ! So the "repeated by values" is not because the data is being stacked (long) and I would have more than one observation for each OBS? It is coming from the original? If so, I would have to find out which one it is, because that would indicate a mistake in my raw data. We are not supposed to have repeated obs for each OBS in the original file. I see only one observation with a value of 1 in column 2 which would mean I only have one repeated Id maybe. Could I use COL2 to find out this quickly? If so, how? Thanks again.

Re: creating a new variable and changing wide to long format

Kurt_Bremser — Wed, 27 Jan 2021 12:54:45 GMT

Just take the OBS value from the observation where COL2 is not missing, and use that to search in your raw data. Checking for duplicates is one part of my Maxim 3 (Know Your Data).

Re: creating a new variable and changing wide to long format

Mscarboncopy — Wed, 27 Jan 2021 14:45:19 GMT

yes, of course. Sometimes I am trying to do things differently and forget the basics. I did find the one id that was indeed incorrectly entered. Col2 is then perfect for me to check for these issues. I really appreciate your help. I have another question. How do I transpose string and date variables? I tried to use the code provided but it did not work. I will give an example because it will add complexity.

I am using the transpose for all the numeric variables. Some of them, not all, have a corresponding string variable. So for example in the example I gave, if med 1 is selected a string one will appear. If med 2 is selected a string 2 will appear and so on. So I would need to create a common var String (instead of string 1, string 2 and so on) that would assign each string to each key selected in the Med var. I would do the same with dates variables that are sometimes asked for the numeric variables the same way I would do the strings. Thank you again.

Re: creating a new variable and changing wide to long format

Kurt_Bremser — Wed, 27 Jan 2021 15:05:42 GMT

The double transpose is only good for variables with similar attributes; for mixed types/attributes (e.g.character, "normal" numeric and numeric/dates), it's better to transpose the variable groups separately and merge back.

Re: creating a new variable and changing wide to long format

Mscarboncopy — Wed, 27 Jan 2021 15:47:23 GMT

Understood. I am planning on doing that. But I am not sure if I need to change the transpose, when I use it for string and dates it does not work. I am wondering if I am doing something wrong.I am using the code provided here and adding the string variable names where you see the numeric variable names. there is probably a change needed for variables that are not numeric?