How to convert groups in rows to seperate columns?

[kenjichan1212]

Hospital                    Cancer           Data

Albany Hospital     Bowel cancer .  2
Albany Hospital     Bowel cancer .  5
Albany Hospital     Bowel cancer    6
Albany Hospital     Breast cancer    7
Albany Hospital     Breast cancer    5

Hi I'm just wondering how to convert the above output to the following in SAS or Proc SQL;

Hospital                    Bowel Cancer    Breast Cancer      

Albany Hospital                      2                     7
Albany Hospital                     5.                      5                
Albany Hospital                     6                                         
   

Thank you!                                  

[PeterClemmensen]

How do you determine which Data value go where? For example, why is Bowel Cancer=2 and Breast Cancer=7 in the first row of your desired output?

Is it because they appear first in each group?

[Shmuel]

May be its not the most elegant way to do it, but you may try:

1) select all cancer types to create a list of the new variables and

    to create code for assigning the data value to the the appropriate variable:

options source source2 symbolgen;
proc sql;
   create table types as select distinct cancer
   from have;
quit;

filename code tmp;
data _NULL_;
  set types end=eof;
       length var_list $1000 var_name $32 a_line $120; /* adapt length to needs */
       retain var_list ' ';
       var_name = translate(cancer,'_',' ');
       var_list = catx(' ',var_list,var_name);

      file code;
      a_line = 'if cancer = " ' || trim(cancer) || ' then ' ||
                    trim(var_name) || '  = data; else ';
      put a_line'

      if eof then do;
         put ';';
         call symput('var_list' , trim(var_list));
      end;
run;

data want;
     keep hospital &var_list;
 set have;
     %include code;
run;

 code is not tested. In case of issues post your code and the full log.

[Ksharp]

Merge Skill proposed by me,Matt,Art.C .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
input  Hospital   & $40.    Cancer  & $40.      Data;
cards;
Albany Hospital     Bowel cancer  2
Albany Hospital     Bowel cancer   5
Albany Hospital     Bowel cancer    6
Albany Hospital     Breast cancer    7
Albany Hospital     Breast cancer    5
;
proc freq data=have noprint;
table cancer/out=key nopercent;
run;

data _null_;
 set key end=last;
 if _n_=1 then call execute('data want; merge ');
 call execute(catt('have(where=(cancer="',cancer,'") 
 rename=(data=',compress(cancer),'))'));
 if last then call execute(';by hospital;output;call missing(of _all_);drop cancer;run;');
 run;

[novinosrin]

Hello @kenjichan1212 

This is one of the classic case to apply double transpose or perhaps @art297 's macro transpose. Though I love Art,  I haven't had the time to read Art's macro yet to feel the intuition before i start applying but here is the double transpose method

data have;
input Hospital        & $30.            Cancer     & $30.           Data;
cards;
Albany Hospital     Bowel cancer        2
Albany Hospital     Bowel cancer   5
Albany Hospital     Bowel cancer    6
Albany Hospital     Breast cancer    7
Albany Hospital     Breast cancer    5
;

proc transpose data=have out=temp;
by hospital cancer ;
var data;
run;

proc transpose data=temp out=want(drop=_name_);
by hospital  ;
var col:;
id cancer;
run;

[art297]

@novinosrin : No, the %transpose macro wasn't designed for this specific type of transpose, but could be used if prefaced by a data step that assigned the desired order. The following would produce the same output file as both your and @Ksharp 's proposed code:

data need;
  set have;
  by Hospital Cancer;
  if first.Cancer then recnum=1;
  else recnum+1;
run;

%transpose(data=need,out=want(drop=recnum),by=hospital recnum,
 id=cancer,convertid=yes,use_varname=no,var=data,sort=yes)

Art, CEO, AnalystFinder.com

[Tom]

Just make a new variable so that you have unique keys.

data have ;
  input Hospital &:$30. Cancer &:$32. DataVar;
cards;
Albany Hospital     Bowel cancer    2
Albany Hospital     Bowel cancer    5
Albany Hospital     Bowel cancer    6
Albany Hospital     Breast cancer    7
Albany Hospital     Breast cancer    5
;

data fix;
  set have;
  by hospital cancer ;
  if first.cancer then row=0;
  row +1;
run;
proc sort; by hospital row; run;

proc transpose data=fix ;
  by hospital row;
  id cancer;
  var datavar;
run;

                                            Bowel_    Breast_
Obs       Hospital        row    _NAME_     cancer     cancer

 1     Albany Hospital     1     DataVar       2         7
 2     Albany Hospital     2     DataVar       5         5
 3     Albany Hospital     3     DataVar       6         .

[Reeza]

For display or data?

proc freq data=have;
table hospital*cancer;
weight data;
run;