topic Re: Help with data manipulation in SAS Enterprise Guide

Help with data manipulation

Jenna25 — Tue, 21 Jul 2020 11:16:02 GMT

Hi there, I am looking for functions or ideas on how to analysis the below example dataset:

What i would like to do it using the colour columns, create a new variable which assigns the favourite colour of each individual. So for person1 a simple IF statement would be fine as they only have 1 favourite colour, I can say 'if red = 1 then fav_colour = red'. However, the issue is when a person has more than one favourite colour i want to be able to assign 2 separate variables that have the 2 different colours in it.

	red	yellow	blue	green	orange	fav_colour	fav_colour2	fav_colour3
person1	1	0	0	0	0	red
person2	0	0	0	0	1	orange
person3	0	1	0	1	0	yellow	green
person4	0	0	1	0	0	blue
person5	1	0	0	1	1	red	green	orange

I have been looking at using arrays and do loops but am struggling to figure out how best to use these, so any ideas on functions that could be useful would be very much appreciated. (I have SAS Enterprise Guide version 7.11)

Thanks!

Re: Help with data manipulation

PeterClemmensen — Tue, 21 Jul 2020 11:23:26 GMT

So, all you have now are the color columns, correct?

Re: Help with data manipulation

PaigeMiller — Tue, 21 Jul 2020 11:27:04 GMT

UNTESTED CODE

If you want tested code, please provide data as a SAS data step and not as a screen capture

data want;
    set have;
    array colors(*) red yellow blue green orange;
    array fav_color(*) $ 8 fav_color1-fav_color5;
    count=1;
    do i=1 to dim(colors);
        if colors(i)=1 then do; 
            fav_color(count)=vname(colors(i));
            count=count+1;
        end;
    end;
    drop i count;
run;

Re: Help with data manipulation

Kurt_Bremser — Tue, 21 Jul 2020 11:23:58 GMT

Two transposes do it:

data have;
infile datalines dlm='09'x;
input person $ red	yellow 	blue 	green	orange;
datalines;
person1	1	0	0	0	0
person2	0	0	0	0	1
person3	0	1	0	1	0
person4	0	0	1	0	0
person5	1	0	0	1	1
;

proc transpose data=have out=long (where=(col1 = 1));
by person;
var _numeric_;
run;

data num;
set long;
by person;
if first.person
then num = 1;
else num + 1;
run;

proc transpose data=num out=wide (drop=_name_ _label_) prefix=fav_colour;
by person;
var _name_;
id num;
run;

data want;
merge
  have
  wide
;
by person;
run;

Re: Help with data manipulation

PaigeMiller — Tue, 21 Jul 2020 13:21:13 GMT

@Jenna25 wrote:

Hi there, I am looking for functions or ideas on how to analysis the below example dataset:

What i would like to do it using the colour columns, create a new variable which assigns the favourite colour of each individual. So for person1 a simple IF statement would be fine as they only have 1 favourite colour, I can say 'if red = 1 then fav_colour = red'. However, the issue is when a person has more than one favourite colour i want to be able to assign 2 separate variables that have the 2 different colours in it.

red yellow blue green orange fav_colour fav_colour2 fav_colour3

person1 1 0 0 0 0 red

person2 0 0 0 0 1 orange

person3 0 1 0 1 0 yellow green

person4 0 0 1 0 0 blue

person5 1 0 0 1 1 red green orange

I have been looking at using arrays and do loops but am struggling to figure out how best to use these, so any ideas on functions that could be useful would be very much appreciated. (I have SAS Enterprise Guide version 7.11)

I question the value of doing this transformation of the data. It seems to me that any analysis you could do with the new columns could be done just as easily on the columns of 0s and 1s.

Re: Help with data manipulation

andreas_lds — Tue, 21 Jul 2020 13:37:40 GMT

Looks like transforming from bad to worse, but if you need it ... do you really want the fav_colour-variables added to the variable you already have, or do you want person and fav_colour only?

If you are happy with person and fav_colours, try this, it uses the have-dataset provided by @Kurt_Bremser

data pre_want;
   set have;
   length fav_colour $ 10;
   
   array colours red--orange;
   
   do i = 1 to dim(colours);
      if colours[i] then do;
         fav_colour = vname(colours[i]);
         output;
      end;
   end;
   
   keep person fav_colour;
run;

proc transpose data=pre_want out=want(drop=_name_) prefix=fav_colour;
   by person;
   var fav_colour;
run;

Re: Help with data manipulation

Jenna25 — Tue, 21 Jul 2020 13:37:29 GMT

Thank you so much, this has done exactly what i needed!