BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
livcat1
Calcite | Level 5

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
 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

Thanks for your help and time

PGStats
Opal | Level 21

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
livcat1
Calcite | Level 5
Thanks PGStats this works perfectly!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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