I have a large data set and want to summarise the count and percent of a number of variables by site then output these values into a new data set with a single row per site.
I have used the following code:
ods output onewayfreqs=work.owf;
proc freq data=euro1;
tables f1q1 f2q3;
by country site;
run;
ods listing;
proc print data=work.owf;
run;
the output looks like this, with multiple rows per site
Obs | Country | Site | Table | F_f1q1 | f1q1 | Frequency | Percent | F_f2q3 | f2q3 |
1 | UK | 42 | Table f1q1 | A | A | 50 | 100 | ||
2 | UK | 42 | Table f2q3 | 19 | 38 | F | F | ||
3 | UK | 42 | Table f2q3 | 31 | 62 | M | M | ||
7 | UK | 50 | Table f1q1 | A | A | 50 | 100 | ||
8 | UK | 50 | Table f2q3 | 22 | 44 | F | F | ||
9 | UK | 50 | Table f2q3 | 28 | 56 | M | M | ||
13 | Germany | 75 | Table f1q1 | B | B | 50 | 100 | ||
14 | Germany | 75 | Table f2q3 | 22 | 44 | F | F |
However, I would like to re-define the frequency and percent variables to relate to the corresponding variable value and to only have one row per site. i.e. this is what I want the data to look like:
Obs | Country | Site | freqf1q1A | freqf1q1B | percf1q1A | percf1q1B | freqf2q3F | freqf2q3M | percf2q3F | percf2q3M |
1 | UK | 42 | 50 | 100 | 19 | 31 | 38 | 62 | ||
2 | UK | 50 | 50 | 100 | 22 | 28 | 44 | 56 | ||
3 | Germany | 75 | 50 | 100 | 22 | 44 |
Any help would be much appreciated thank you!
Catrin
Here is an example using sashelp.cars:
proc freq data=sashelp.cars noprint;
by make type;
tables DriveTrain / out=carsFreq_DT;
tables Cylinders / out=carsFreq_Cyl;
run;
option missing=' ';
data long;
set carsFreq_: indsname=_dsName;
level = scan(_dsName,3,"._");
var = cats("freq", level, DriveTrain, Cylinders);
value = count;
output;
var = cats("perc", level, DriveTrain, Cylinders);
value = percent;
output;
keep make type var value;
format value bestd6.2;
run;
proc sort data=long; by make type var; run;
proc transpose data=long out=report(drop=_name_);
by make type;
id var;
var value;
run;
Post test data in the form of a datastep, this then conveys structure and means we don't have to type that out.
As such this is untested:
data want (keep=country site rename=(res1=freqf1q1a....)); /* note rename all resN variables to what you have in your output dataset */ set have; array res{8}; by site; retain res:; if first.site then call missing(of res:); if f_f1q1 = "A" then do; res{1}=frequency; res{3}=percent; end; else if f+f1q1="B" then do; res{2}=frequency; res{4}=percent; end; else if f_f2q3="F" then do; res{5}=frequency; res{7}=percent; end; else do; res{6}=frequency; res{8}=percent; end; if last.site then output; run;
Thanks for your help and time
Here is an example using sashelp.cars:
proc freq data=sashelp.cars noprint;
by make type;
tables DriveTrain / out=carsFreq_DT;
tables Cylinders / out=carsFreq_Cyl;
run;
option missing=' ';
data long;
set carsFreq_: indsname=_dsName;
level = scan(_dsName,3,"._");
var = cats("freq", level, DriveTrain, Cylinders);
value = count;
output;
var = cats("perc", level, DriveTrain, Cylinders);
value = percent;
output;
keep make type var value;
format value bestd6.2;
run;
proc sort data=long; by make type var; run;
proc transpose data=long out=report(drop=_name_);
by make type;
id var;
var value;
run;
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.