transpose data and use data value as variable name

[livcat1]

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
 

[RW9]

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;

[livcat1]

Thanks for your help and time

[PGStats · Accepted solution]

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;

[livcat1]

Thanks PGStats this works perfectly!