topic Re: How to convert groups in rows to seperate columns? in SAS Programming

How to convert groups in rows to seperate columns?

kenjichan1212 — Wed, 17 Jul 2019 07:06:18 GMT

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!

Re: How to convert groups in rows to seperate columns?

PeterClemmensen — Wed, 17 Jul 2019 07:11:35 GMT

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?

Re: How to convert groups in rows to seperate columns?

Shmuel — Wed, 17 Jul 2019 10:20:55 GMT

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.

Re: How to convert groups in rows to seperate columns?

Ksharp — Wed, 17 Jul 2019 11:56:57 GMT

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;

Re: How to convert groups in rows to seperate columns?

novinosrin — Wed, 17 Jul 2019 11:59:20 GMT

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;

Re: How to convert groups in rows to seperate columns?

art297 — Wed, 17 Jul 2019 13:33:30 GMT

@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

Re: How to convert groups in rows to seperate columns?

Tom — Wed, 17 Jul 2019 14:56:36 GMT

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         .

Re: How to convert groups in rows to seperate columns?

Reeza — Wed, 17 Jul 2019 15:28:09 GMT

For display or data?

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