topic Re: Put row info to columns with yes and no in SAS Programming

Put row info to columns with yes and no

jonatan_velarde — Sat, 26 Sep 2020 13:55:06 GMT

Good day SAS friends:

i have a data set containing information from individuals from school, and i know what they eat at lunch, each information is separated by semicolon ";", as follows

data have;

input student answers$;

cards;

1 bread; soda

2 bread; soda; water

3 water; bread

4 soda; crackers

5 crackers

6 crakers; water

;

student	bread	soda	water	crackers
1	yes	yes	no	no
2	yes	yes	yes	no
3	yes	no	yes	no
4	no	yes	no	yes
5	no	no	no	yes
6	no	no	yes	yes

Thanks in advance

the new data set that i want to create based in this is:

Re: Put row info to columns with yes and no

Kurt_Bremser — Sat, 26 Sep 2020 14:28:29 GMT

First, create a long dataset:

data long;
set have;
answer = "yes";
do i = 1 zo countw(answers,';');
  meal = scan(answers,i,';');
  output;
end;
keep student meal answer;
run;

Then transpose to wide:

proc transpose data=long out=wide;
by student;
var answer;
id meal;
run;

If necessary, you can replace any missing values in a follow-up step with "no". The beauty of the transpose is that you do not need to know all the meals beforehand.

Re: Put row info to columns with yes and no

novinosrin — Sat, 26 Sep 2020 18:23:24 GMT

Hi @jonatan_velarde By all means @Kurt_Bremser 's solution is indeed clean as it is data driven that gives you the comfort of not having to be concerned about what's in your data. However, should you know beforehand that there exists only 4 distinct lunch items in your data, i.e.

bread

soda

water

crackers

you may consider going lazy declaring an array like-

data have;
input student answers & $20.;
cards4;
1 bread; soda
2 bread; soda; water
3 water; bread
4 soda; crackers
5 crackers
6 crakers; water
;;;;

data want;
 set have;
 array food(*)$10 bread	soda water crackers;
 array t(4) $10 _temporary_ ('bread','soda','water','crackers');
 do _n_=1 to dim(food);
  food(_n_)=ifc(find(answers,strip(t(_n_))),'YES','NO');
 end;
 drop answers;
run;