topic Re: Output freq on multiple vars into one data set in SAS Programming

Output freq on multiple vars into one data set

Ronein — Mon, 09 Jun 2025 10:11:42 GMT

Hello

I want to do freq on multiple vars.

What is the reason that for var sex (the var that i defined) I dont see values in resulted data set???

DATA CLASS(drop=sex rename=(_sex=sex));
SET sashelp.class;
IF SEX='F' THEN _SEX='(a1) F';
else _Sex='(a2) M';
Run;

ods output onewayfreqs=work.owf;
proc freq data=CLASS ;
tables _all_/nocum nopercent ;
run;
data work.owf(drop=frequency) ;
set work.owf ;
count=frequency;
run;
data work.owf;
length v_name v_value $ 200;
set work.owf;
v_name=scan(table,2);
v_value=scan(catx(' ',of name--weight),1);
*name is the first variable,weight is the last variable in sashelp.class table;
keep v_name v_value count;
run;

Re: Output freq on multiple vars into one data set

PaigeMiller — Mon, 09 Jun 2025 11:58:58 GMT

To simplify the whole program

ods output onewayfreqs=work.owf(rename=(frequency=count));
proc freq data=sashelp.CLASS ;
tables _all_/nocum nopercent ;
run;

data work.owf2;
length v_name v_value $ 200;
set work.owf;
v_name=scan(table,2);
v_value=scan(catx(' ',of sex name--weight ),1,' ');
*name is the first variable,weight is the last variable in sashelp.class table;
keep v_name v_value count;
run;

May I ask what is the reason why you are complicating things by creating new sexes '(a1) F' and '(a2) M' ? I have never heard of these, I'm sure most people don't know what these cryptic codes a1 and a2 mean, so I have left these out of the solution. If you really need them, put them back into my code.

Another simplification would be not to rename FREQUENCY to COUNT, I don't see the point of taking the time to do that either, but you don't need a DATA step to do that rename.

Re: Output freq on multiple vars into one data set

Kathryn_SAS — Mon, 09 Jun 2025 12:03:54 GMT

The value of the Sex variable is blank because of this statement:

v_value=scan(catx(' ',of name--weight),1); *name is the first variable,weight is the last variable in sashelp.class table;

Weight is not the last variable in the data set since you created _sex and then renamed it to Sex. You either need to use name--sex, or I would suggest the following revised code:

data work.owf1;
retain v_name v_value;
length v_name v_value $ 200;
set work.owf;
v_name=scan(table,2);
v_value=trim(left(vvaluex(v_name)));*catx(' ',of name--sex);
*name is the first variable,weight is the last variable in sashelp.class table;
keep v_name v_value count;
run;

Re: Output freq on multiple vars into one data set

PaigeMiller — Mon, 09 Jun 2025 12:11:06 GMT

Good point @Kathryn_SAS , adding VVALUEX into the code is a much better way to go here.

Re: Output freq on multiple vars into one data set

Tom — Mon, 09 Jun 2025 12:16:30 GMT

Are you sure that SEX is one of the variables included in the positional variable list NAME--WEIGHT that you passed to the CATX() function? It should have been created as after the WEIGHT variable in the dataset CLASS you made with your first data step. Run PROC CONTENTS with the VARNUM option to see the actual order of the variables in that dataset.

And even if you did include it in the string you generated with the CATX() function call you put spaces into the values of SEX so that when you later tell scan to use space as one of the multiple characters it considers as a delimiter then it will treat the values of SEX as two separate words . So even if you did find the value of SEX the values like

(a1) F

will only return (a1) from the SCAN() function.

But why are you using CATX() and SCAN() here at all? That should not work properly for a number of reasons. First the CATX() function ignores the missing values when generating the string. Second only one of the variables should have a none missing value, ten one whose name you just found in the previous statement. You might just use CATS() instead? But why not just use VVALUEX() to find the value of the variable whose name you just got?

v_name=scan(table,2);
v_value=vvaluex(v_name);

Re: Output freq on multiple vars into one data set

Tom — Mon, 09 Jun 2025 12:18:40 GMT

Why would you include the TRIM() function in this statement?

v_value=trim(left(vvaluex(v_name)));

SAS will just append the trailing spaces back onto the value when you store it into the fixed length variable V_VALUE.

Re: Output freq on multiple vars into one data set

Kathryn_SAS — Mon, 09 Jun 2025 12:26:40 GMT

You are right that TRIM is not necessary. I am just in the habit of using both when aligning numeric and character values in a column. Thanks for pointing that out!

Re: Output freq on multiple vars into one data set

Ronein — Tue, 10 Jun 2025 05:00:28 GMT

Hello,

This code work well

DATA CLASS(drop=sex rename=(_sex=sex));
SET sashelp.class;
IF SEX='F' THEN _SEX='(a1) F';
else _Sex='(a2) M';
Run;

ods output onewayfreqs=freq_tbl1;
proc freq data=CLASS ;
tables _all_  /**Name Age   Height   Weight    Sex**//nocum  ;
run;

ods output close;  *technically unneeded but makes it more clear;
data freq_tbl2(KEEP=var_name value Frequency Percent);
retain var_name value Frequency Percent;
set freq_tbl1;
value = left(coalescec(of f_:));
var_name=scan(table,2); /**Take second word from field table**/
run;