topic Consolidating multiple observation(2 observation) to variable(2 variable) in SAS Procedures

Consolidating multiple observation(2 observation) to variable(2 variable)

kk1992nsn — Wed, 12 Feb 2014 15:39:12 GMT

Hello,

I have 4 observation,which having var1 as unique key,var2 can have only 2 values like A and B. How can i create two more separate variables so that one variable flag if var 2 has value 'A' and other will flag if var 2 has value 'B'. Sorry for my bad English.

Re: Consolidating multiple observation(2 observation) to variable(2 variable)

ballardw — Wed, 12 Feb 2014 16:07:06 GMT

It will help if you can post an example of the data you have and then the output you want.

Re: Consolidating multiple observation(2 observation) to variable(2 variable)

kk1992nsn — Wed, 12 Feb 2014 17:16:21 GMT

Available Data

Clm ID Task Template

1 16

1 17

3 16

4 17

Desired Data

Clm ID TemNum16 TempNum17

1 Y Y

3 Y N

4 N Y

Please Suggest

Re: Consolidating multiple observation(2 observation) to variable(2 variable)

overmar — Sat, 15 Feb 2014 20:55:27 GMT

data have2;

set have;

if task = 16 then tempnum16 = "Y";

else tempnum16 = "N";

if task = 17 then tempnum17 = "Y";

else tempnum17 = "N";

run;

proc sql;

create table want as

select distinct clm_id, (min(tempnum16)) as tempnum16, (min(tempnum17)) as tempnum17

from have2

group by clm_id;

quit;

Normally I would use max rather than min, but Y comes after N, so you use min for the summary of the responses.

Re: Consolidating multiple observation(2 observation) to variable(2 variable)

Tom — Sat, 15 Feb 2014 23:22:45 GMT

Proc Transpose can do that, but you will need to add a step to but in the 'N's for the missing values.

data have ;

input id template @@;

dummy='Y';

cards4;

1 16 1 17 3 16 4 17

;;;;

proc transpose data=have out=want prefix=TempNum ;

by id ;

id template;

var dummy ;

run;

data want ;

set want ;

drop _name_;

array c TempNum: ;

do over c; c=coalescec(c,'N'); end;

run;