Help using Base SAS procedures

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

Reply
New Contributor
Posts: 2

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

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.

Super User
Posts: 11,343

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

Posted in reply to kk1992nsn

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

New Contributor
Posts: 2

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

Posted in reply to kk1992nsn

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

Super User
Super User
Posts: 7,059

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

Posted in reply to kk1992nsn

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;

Frequent Contributor
Posts: 83

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

Posted in reply to kk1992nsn

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.

Ask a Question
Discussion stats
  • 4 replies
  • 199 views
  • 0 likes
  • 4 in conversation