add texts to some rows in proc report

[lc7033907]

Hello everyone,

I have a dataset work.test,

data test;
  input text $ Freq;
  datalines;
F  10
M  20
Red 15
Yellow 10
Blue 20
;

now I want to get a report like the following:

                 Freq

Gender
  Female         10
  Male           20

Color
   Red           15
   Yellow        10
   Blue          20

Any suggestion? Thanks.                                    

[librasonali]

edited : 26/12/2020 -- 18:58 IST 

Do you mean that you want your output be like below ?

Freq	Gender	Color
10	Female	Yellow
15		Red
20	Male	Blue

if above is true then below code will work 

Data test;
input Text $ Freq;
datalines;
F 10
M 20
Red 15
Yellow 10
Blue 20
;
run;

proc print data =test;
run;

data Gender_DATA ;
set test;
if Text='F' then Text = "Female";
else if Text='M' then Text = "Male";
if Text in ('Female','Male') then do Gender=Text ;
output ;
end;
drop Text  ;
run;

data Color_DATA;
set test;
if Text not in ('F','M') then do Color=Text ;
output;  
end;
drop Text ;
run;

/*sorting the above datasets for merge */

proc sort data=gender_data;
by Freq;
run;

proc sort data=color_data;
by Freq;
run;

data final;
merge gender_data color_data;
by freq;
run;

ods csv file="/home/u49388206/cert/excel/DATA.csv";
proc report data=Final ;
title "Sorted Data";
ods csvall close;

[singhsahab]

data test;
  input text $ Freq;
  datalines;
F  10
M  20
Red 15
Yellow 10
Blue 20
;
run;

proc sql;
create table have1 as
select Freq,
       case when text eq 'F' then 'Female' 
            when text eq 'M' then 'Male' end as Gender
	        from test where calculated Gender ne '';
create table have2 as
select Freq,
       case when text not in ('F','M') then text end as color 
       from test where calculated color ne '';
create table want as
select COALESCE(a.freq,b.freq) as Freq,Gender,b.color from have1 as a full join have2 as b on a.freq =b.freq;
quit;



           

Want dataset is your final data. you can use any export technique to take in desired output format. 

[librasonali]

THANKS FOR THE PROC SQL VERSION Answer for this question !

[lc7033907]

Hello, thank you for your reply. I tried your method, but it's not what I
want. With your code, there is one column, Gender, no value in it. I want
to add categorization words before text. For example, one row called
"Gener", then Female and male are underneath it with some indent. Then
"Color" and the rest three underneath it.

[singhsahab]

Can you post a screen shot of your desire output?

[librasonali]

/*UPDATED CODE*/


Data test;
input Text $ Freq;
datalines;
F 10
M 20
Red 15
Yellow 10
Blue 20
;
run;

proc print data =test;
run;

data Gender_DATA ;
set test;
if Text='F' then Text = "Female";
else if Text='M' then Text = "Male";
if Text in ('Female','Male') then do Gender=Text ;
output ;
end;
drop Text  ;
run;

data Color_DATA;
set test;
if Text not in ('F','M') then do Color=Text ;
output;  
end;
drop Text ;
run;

/*sorting the above datasets for merge */

proc sort data=gender_data;
by Freq;
run;

proc sort data=color_data;
by Freq;
run;

data final;
merge gender_data color_data;
by freq;
run;

/* This is will create report*/
PROC TABULATE data=Final;
CLASS  Gender Color / MISSING;
var freq;
TABLE freq , Gender, Color;
RUN;

OUTPUT : 

from your reply I can figure out that above is the expected output you desire.. ??

[Tom · Accepted solution]

The first thing you need to do is convert that data into something that more actually reflects the data.  Currently there is no way to know which variable each row represents.  For example you could add a column that shows the variable name.

data real_data ;
  length varname $32 ;
  set test;
  if _n_ in (1:2) then varname='Gender';
  else varname='Color';
run;

results

Obs    varname    text      Freq

 1     Gender     F          10
 2     Gender     M          20
 3     Color      Red        15
 4     Color      Yellow     10
 5     Color      Blue       20

Now you can use VARNAME as an group/order variable in PROC REPORT.

proc report data=real_data ;
  columns varname text freq ;
  define varname / order order=data noprint;
  define text / order order=data ' ';
  compute before varname ;
     line @1 varname $32. ;
  endcomp;
run;

Results

                 Freq
Gender
  F                10
  M                20
Color
  Red              15
  Yellow           10
  Blue             20