DATA Step, Macro, Functions and more

transpose data and use data value as variable name

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

transpose data and use data value as variable name

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  

ObsCountrySiteTableF_f1q1f1q1FrequencyPercentF_f2q3f2q3
1UK42Table f1q1AA50100  
2UK42Table f2q3  1938FF
3UK42Table f2q3  3162MM
7UK50Table f1q1AA50100  
8UK50Table f2q3  2244FF
9UK50Table f2q3  2856MM
13Germany75Table f1q1BB50100  
14Germany75Table f2q3  2244FF

 

 

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:

ObsCountrySitefreqf1q1Afreqf1q1Bpercf1q1Apercf1q1Bfreqf2q3Ffreqf2q3Mpercf2q3Fpercf2q3M
1UK4250 100 19313862
2UK5050 100 22284456
3Germany75 50 1002244  


Any help would be much appreciated thank you!

Catrin
 


Accepted Solutions
Solution
‎08-29-2017 02:02 AM
Respected Advisor
Posts: 4,927

Re: transpose data and use data value as variable name

[ Edited ]

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;

 

PG

View solution in original post


All Replies
Super User
Super User
Posts: 7,970

Re: transpose data and use data value as variable name

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;
New Contributor
Posts: 3

Re: transpose data and use data value as variable name

Thanks for your help and time

Solution
‎08-29-2017 02:02 AM
Respected Advisor
Posts: 4,927

Re: transpose data and use data value as variable name

[ Edited ]

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;

 

PG
New Contributor
Posts: 3

Re: transpose data and use data value as variable name

Thanks PGStats this works perfectly!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 225 views
  • 0 likes
  • 3 in conversation