topic Re: Modifying how to print a dataset in SAS Programming

Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 21:03:50 GMT

I am putting some summary tables in datasets to print for a report. Below, I copied the code to save the label to come with the datasets:

ods output OneWay=Output; 
proc surveyfreq data = &dataset VARHEADER = NAMELABEL nosummary;
tables &varname/cl nofreq nostd;
weight &weight_var;
run;

data output;
set output;
label = vlabelx(scan(table,2,' '));
run;

I wanted to keep the labels; however, I do not want the labels to be on every row of my tables.

Is there any way I can print the label only once? Please see an example of my output report below:

AGE	label	Frequency	Percent	L_95_p_CI	U_95_p_CI
10-20 years	Age categories	379	19.5	17.1	21.9
21-30 years	Age categories	460	23.7	21.3	26.1
31-40 years	Age categories	380	19.6	17.6	21.5
41-50 years	Age categories	369	19.0	17.0	20.9
51-60 years	Age categories	354	18.3	16.6	19.9

Thanks

Re: Modifying how to print a dataset

PaigeMiller — Tue, 27 Apr 2021 20:50:24 GMT

PROC PRINT and PROC REPORT allow you to choose which data set variables get printed;

proc print data=yourdatasetname;
var age frequency percent l_95_p_ci u_95_p_ci;
run;

Re: Modifying how to print a dataset

Reeza — Tue, 27 Apr 2021 20:55:15 GMT

You're aware that those two code segments are not connected at all?

Where do you want to see the label? I'd assume that instead of "AGE" you want to see "Age Categories".

Add the LABEL option to the PROC PRINT statement to see the labels and drop the Label column entirely.

Re: Modifying how to print a dataset

ballardw — Tue, 27 Apr 2021 21:01:35 GMT

Proc Report would show the default assigned variable label as a column heading.

This adds a new label to the variable name in a data set you should have available to demonstrate.

data work.class;
   set sashelp.class;
   label name='Student name';
run;

proc report data=work.class;
   columns name sex age;
run;

Or you can provide a label to override the existing one in a define statement:

proc report data=work.class;
   columns name sex  age ;
   define age/ 'Age at enrollment';
run;

Or use a variable as a Group variable

proc report data=work.class;
   columns sex name  age ;
   define sex / group;
   define age/ 'Age at enrollment';
run;

Though Proc Report would want the "group" variable text to appear to the left of the categories. Note that the Columns statement provides the order left to right of the columns that appear in the output.

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 21:06:06 GMT

Thank you, PaigeMiller. But that removes the label completely. I want it but only once not on each row. Thanks

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 21:10:09 GMT

Thank you Reeza, for your suggestions and for noticing the missing part in my code. I connected the code in my original post.

I want to keep Age, but somewhere I would like to have the label as well. Age is the name of the variable and I want to keep them for easy access to variable names when reading the report.

After using the ODS OUTPUT, Label is a column of my data and not the label of a variable anymore.
Thanks

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 21:15:09 GMT

Thank you, ballardw, for your suggestion. I want to keep the variable name age as is. I want to add the label in some form once. the label is not really a label anymore. It is a column of my dataset that is repeating itself for all rows, which makes it too repetitive in a long report. Thanks

Re: Modifying how to print a dataset

Patrick — Tue, 27 Apr 2021 21:29:36 GMT

SAS makes it hard for us to do things that are not logical.

You've shown us so far only your not desired report. How should your desired report look like?

Re: Modifying how to print a dataset

ballardw — Tue, 27 Apr 2021 21:31:25 GMT

@Emma_at_SAS wrote:
Thank you, ballardw, for your suggestion. I want to keep the variable name age as is. I want to add the label in some form once. the label is not really a label anymore. It is a column of my dataset that is repeating itself for all rows, which makes it too repetitive in a long report. Thanks

That is exactly what a GROUP variable does in proc report.

Provide and actual example of your data set in the form of data step code and I'll show you how. Though the last example I provided should work: change the data set to yours, your variables on the columns state with the Label variable first, Define the Label variable as a Group variable.

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 21:54:17 GMT

Good point, Patrik. My ideal table looks like this:

Age categories

AGE	Frequency	Percent	L_95_p_CI	U_95_p_CI
10-20 years	379	19.5	17.1	21.9
21-30 years	460	23.7	21.3	26.1
31-40 years	380	19.6	17.6	21.5
41-50 years	369	19.0	17.0	20.9
51-60 years	354	18.3	16.6	19.9

Thanks

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 21:59:09 GMT

Thanks, ballardw. I try your previous code and will send an update shortly. Thanks

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 22:13:44 GMT

Thanks, ballardw. I tried the GROUP code but I think I am doing something wrong because it grouped all rows. My code and the new table are copied below. I am going to send my full code in a separate post. Thanks

proc report data=output3;
   columns label &varname Frequency Percent L_95_p_CI U_95_p_CI  ;
   define label / group;
run;

label	Age categories	Weighted Frequency	Percent	95% Lower Confidence Limit, Percent	95% Upper Confidence Limit, Percent
Age categories	15	1941	100.0	89.7	110.3

Re: Modifying how to print a dataset

Emma_at_SAS — Tue, 27 Apr 2021 22:19:10 GMT

Below is the full code that I am using. I hope it helps. Thanks

%macro macro_surveyfreq(varname);
ods exclude all;
ods output OneWay=Output; 
proc surveyfreq data = &dataset VARHEADER = NAMELABEL nosummary;
    tables &varname/cl nofreq nostd; 
	weight &weight_var;
run;
data output;
    set output;
    label = vlabelx(scan(table,2,' '));
run;
proc print data=output; run;

data output1; 
set output (drop=Table F_&varname _SkipLine) end=last; 
if not last then output;
run;

data output2 (rename= (WgtFreq=Frequency LowerCL=L_95_p_CI UpperCL=U_95_p_CI));
set output1;
run;
proc print data=output2 noobs; run;

data output3;
   retain &varname 
	label
	Frequency
	Percent 
	L_95_p_CI 
	U_95_p_CI ;
   set output2;
run;

ods exclude none;

proc print data=output3 noobs; run; *this is the table that goes into report;

%mend;

ods word file="D:\path\PROC_SURVEYFREQ.docx" STARTPAGE=NO;
%macro_surveyfreq(age);
%macro_surveyfreq(gender_3_levels);
%macro_surveyfreq(education_level);
%macro_surveyfreq(income_4_cat);
*my list is long here;
ods word close;

Re: Modifying how to print a dataset

Tom — Tue, 27 Apr 2021 23:04:58 GMT

Seems simple enough.

Since you didn't provide any example data let's convert your posted table into a dataset.

data have ;
 infile cards truncover ;
input 
(AGE
label
Frequency
Percent
L_95_p_CI
U_95_p_CI ) ($50./);
cards;
10-20 years
Age categories
379
19.5
17.1
21.9
21-30 years
Age categories
460
23.7
21.3
26.1
31-40 years
Age categories
380
19.6
17.6
21.5
41-50 years
Age categories
369
19.0
17.0
20.9
51-60 years
Age categories
354
18.3
16.6
19.9
;

So just use BY statement. For example with PROC REPORT.

proc report data=have ;
 by label ;
 column  age 
Frequency
Percent
L_95_p_CI
U_95_p_CI 
 ;
 define _all_  / display;
run;

Results

Re: Modifying how to print a dataset

Reeza — Tue, 27 Apr 2021 23:47:38 GMT

%macro macro_surveyfreq(varname);
ods exclude all;
ods output OneWay=Output; 
proc surveyfreq data = &dataset VARHEADER = NAMELABEL nosummary;
    tables &varname/cl nofreq nostd; 
	weight &weight_var;
run;
data output;
    set output;
    label = vlabelx(scan(table,2,' '));
run;
proc print data=output; run;

data output1; 
set output (drop=Table F_&varname _SkipLine) end=last; 
if not last then output;
run;

data output2 (rename= (WgtFreq=Frequency LowerCL=L_95_p_CI UpperCL=U_95_p_CI));
set output1;
run;
proc print data=output2 noobs; run;

proc sql noprint;
select distinct label into :title_name from output2;
quit;

data output3;
   retain &varname 
	Frequency
	Percent 
	L_95_p_CI 
	U_95_p_CI ;
   set output2;
drop label;
run;

ods exclude none;

title j=l "&title_name.";
proc print data=output3 noobs; run; *this is the table that goes into report;

%mend;

Re: Modifying how to print a dataset

Emma_at_SAS — Wed, 28 Apr 2021 14:55:23 GMT

Thank you, everyone!

For more information please review the last post from Tom and Reeza.

In summary, an efficient way to remove repeated labels and add the label at the top of the table is to save the label, for example in title_name

proc sql noprint;
select distinct label into :title_name from output2;
quit;

and then call that title_name in a title when printing the output

title j=l "&title_name.";

Thanks

Re: Modifying how to print a dataset

Emma_at_SAS — Wed, 28 Apr 2021 14:56:21 GMT

Thank you, Tom. This is great!

Re: Modifying how to print a dataset

Emma_at_SAS — Wed, 28 Apr 2021 14:57:07 GMT

Thank you, Reeza. I used this in my code and it works perfectly.

Re: Modifying how to print a dataset

Emma_at_SAS — Thu, 29 Apr 2021 16:19:47 GMT

Reeza, thank you again for your code. Now, I want to use it on another occasion but I could not modify it very well.

I am using the following code to save the labels and print them above the tables in my report. In my SAS output, it works perfect and copies each label above each table (sample output below)

AGE: How old are you?

VarName	Mean	SD	L_95_p_CI	U_95_p_CI	N
AGE	39.88	14.25	39.14	40.62	1250

However, in my ODS WORD file, only the label for the first variable in the list appears. May you please help me to resolve this issue, please? Thanks

%macro do_sm(varname);
ods exclude all;
data output3;
   retain VarName 
	VarLabel
	Mean
	&varname
	LowerCLMean
	UpperCLMean
	N;
   set output2;
run;

data output4 (rename=(VarName=VarName
						   VarLabel=Label
						   &varname=SD
						   LowerCLMean=L_95_p_CI 
						   UpperCLMean=U_95_p_CI));
set output3;
run;

proc sql noprint;
select distinct Label into :title_name from output4;
quit;

data output5 (drop=Label); set output4; run;

ods exclude none;

title j=l "&title_name.";
proc print data=output5 noobs; run;

             
%mend do_sm;

ods word file="D:path\REPORT_&DATE..docx" STARTPAGE=NO;
OPTIONS NONUMBER NODATE FORMDLIM='~';
%do_sm(age);
%do_sm(money);
ods word close;

Re: Modifying how to print a dataset

Reeza — Thu, 29 Apr 2021 16:35:56 GMT

Reset title name between runs so set it to missing before the PROC SQL. I'm guessing that money doesn't actually have a label so it stays with the old value.

*set to missing;
%let title_name = ;
proc sql noprint;
select distinct Label into :title_name from output4;
quit;