topic Re: Export a Excel file using variable label name and display value in format in SAS Programming

Export a Excel file using variable label name and display value in format

leehsin — Mon, 08 Jul 2019 19:41:52 GMT

Hi, I have a data set which contains data values including dollars, percentages which are displayed using defined format (dollar10.2, percentn8.2). Also the variable names have their labels (no underscore '_'). I wanted to export the data set to an Excel file (.xlsx) which has (1) the values displayed as the same as those format in SAS data set, and (2) the column name using the labels of the variable. It seems to me that I can accomplish the first one using ods excel, and the second one using proc export, but I didn't find a way to do these two at the same time. Asking for help to solve it. Thanks.

Re: Export a Excel file using variable label name and display value in format

Reeza — Mon, 08 Jul 2019 19:43:16 GMT

You can do both with ODS EXCEL, just use PROC PRINT or REPORT to display the data. Within PRINT use the label option to have the labels displayed instead of the variable names.

Re: Export a Excel file using variable label name and display value in format

leehsin — Mon, 08 Jul 2019 19:54:56 GMT

I don't have label statements in the code. The label option after proc print did not display the variable labels in the output.

Re: Export a Excel file using variable label name and display value in format

Reeza — Mon, 08 Jul 2019 19:55:52 GMT

Show your code then, you likely don't have something set correctly. I'm 100% sure this method does work for both labels and formats.

Re: Export a Excel file using variable label name and display value in format

leehsin — Mon, 08 Jul 2019 20:45:13 GMT

My code:

proc sql;
	create table codefees as
	select 
			a.Code format $7. informat $7. length 7
            ,b.Description
            ,b.Count as AR_Count label="AR Counts"
			,a.ST1 as AR_AMT format dollar10.2 label="AR Amount"
			,a.ST2 as MS_AMT format dollar10.2 label="MS Amount"
			,round(a.ST1-ST2, .01) as MS_AMT_DIFF format dollar10.2 label="MS Amount Diff"
			,round(calculated MS_AMT_DIFF/a.ST1, .01) as MS_AMT_PCT format percentn8.2 label="MS Percent Diff"
			,a.ST3 as MO_AMT format dollar10.2 label="MO Amount"
			,round(a.ST1-ST3, .01) as MO_AMT_DIFF format dollar10.2 label="MO Amount Diff"
			,round(calculated MO_AMT_DIFF/a.ST1, .01) as MO_AMT_PCT format percentn8.2 label="MO Percent Diff"
			,a.ST4 as OK_AMT format dollar10.2 label="OK Amount"
			,round(a.ST1-ST4, .01) as OK_AMT_DIFF format dollar10.2 label="OK Amount Diff"
			,round(calculated OK_AMT_DIFF/a.ST1, .01) as OK_AMT_PCT format percentn8.2 label="OK Percent Diff"
			,a.ST5 as TX_AMT format dollar10.2 label="TX Amount"
			,round(a.ST1-ST5, .01) as TX_AMT_DIFF format dollar10.2 label="TX Amount Diff"
			,round(calculated TX_AMT_DIFF/a.ST1, .01) as TX_AMT_PCT format percentn8.2 label="TX Percent Diff"          
			,a.ST6 as LA_AMT format dollar10.2 label="LA Amount"
			,round(a.ST1-ST6, .01) as LA_AMT_DIFF format dollar10.2 label="LA Amount Diff"
			,round(calculated LA_AMT_DIFF/a.ST1, .01) as LA_AMT_PCT format percentn8.2 label="LA Percent Diff"

	from CodeFees_1m as a
    left join Codefees_2 as b
	on a.Code = b.Code
	;
quit;


ods excel file="&root.Procedure\Revised_table.xlsx"
options(autofilter="1-19" sheet_name="State Comparison");

proc print data=codefees label;
run;

ods excel close;

Re: Export a Excel file using variable label name and display value in format

Reeza — Mon, 08 Jul 2019 20:52:39 GMT

And you're saying the Excel file doesn't have the labels in the data set exported, can you show a quick screen shot of what is being generated. I'm also assuming you're using SAS 9.4TS1M3+ - ODS Excel was only production after M2.

Re: Export a Excel file using variable label name and display value in format

ballardw — Mon, 08 Jul 2019 21:16:44 GMT

@leehsin wrote:

My code:

proc sql;
	create table codefees as
	select 
			a.Code format $7. informat $7. length 7
            ,b.Description
            ,b.Count as AR_Count label="AR Counts"
			,a.ST1 as AR_AMT format dollar10.2 label="AR Amount"
			,a.ST2 as MS_AMT format dollar10.2 label="MS Amount"
			,round(a.ST1-ST2, .01) as MS_AMT_DIFF format dollar10.2 label="MS Amount Diff"
			,round(calculated MS_AMT_DIFF/a.ST1, .01) as MS_AMT_PCT format percentn8.2 label="MS Percent Diff"
			,a.ST3 as MO_AMT format dollar10.2 label="MO Amount"
			,round(a.ST1-ST3, .01) as MO_AMT_DIFF format dollar10.2 label="MO Amount Diff"
			,round(calculated MO_AMT_DIFF/a.ST1, .01) as MO_AMT_PCT format percentn8.2 label="MO Percent Diff"
			,a.ST4 as OK_AMT format dollar10.2 label="OK Amount"
			,round(a.ST1-ST4, .01) as OK_AMT_DIFF format dollar10.2 label="OK Amount Diff"
			,round(calculated OK_AMT_DIFF/a.ST1, .01) as OK_AMT_PCT format percentn8.2 label="OK Percent Diff"
			,a.ST5 as TX_AMT format dollar10.2 label="TX Amount"
			,round(a.ST1-ST5, .01) as TX_AMT_DIFF format dollar10.2 label="TX Amount Diff"
			,round(calculated TX_AMT_DIFF/a.ST1, .01) as TX_AMT_PCT format percentn8.2 label="TX Percent Diff"          
			,a.ST6 as LA_AMT format dollar10.2 label="LA Amount"
			,round(a.ST1-ST6, .01) as LA_AMT_DIFF format dollar10.2 label="LA Amount Diff"
			,round(calculated LA_AMT_DIFF/a.ST1, .01) as LA_AMT_PCT format percentn8.2 label="LA Percent Diff"

	from CodeFees_1m as a
    left join Codefees_2 as b
	on a.Code = b.Code
	;
quit;


ods excel file="&root.Procedure\Revised_table.xlsx"
options(autofilter="1-19" sheet_name="State Comparison");

proc print data=codefees label;
run;

ods excel close;

A minor point but you likely want to add NOOBS to the proc print if you want filters all of the variables. Or use autofilter='all'. Your data has 19 variables and the observation number column will use one of the filter columns, #1, leaving your last variable LA_AMT_PCT without a filter.

I would suspect if your output is not getting the labels that you may have either 1) not actually replaced an existing CODEFEES data set or the output file name has some issue and your are possibly looking at a previously generated file.

Re: Export a Excel file using variable label name and display value in format

leehsin — Tue, 09 Jul 2019 16:42:57 GMT

Yes, you are right. I closed all the programs and re-run the code, I got the right variable names. Also thank you for reminding me adding NOOBS option.