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: 10,516

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

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)

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: 6,502

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

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)

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
  • 196 views
  • 0 likes
  • 4 in conversation