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.
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
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;
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.
/*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.. ??
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.