topic Re: export variable names and labels both in excel in SAS Programming

export variable names and labels both in excel

help09 — Fri, 16 Nov 2012 06:25:30 GMT

I tried to export both variable names along with labels using proc export but unable to do so

can you please help me in exporting both

data test;
	input id var1 var2;

label id='sid' var1='value1' var2='value2';

	cards;
	01 2 3
	02 4 4
	03 4 2
	04 2 1
	05 2 3
	06 3 9
	;
	run;

proc export data=test

outfile='C:\mywork\output'

dbms=CSV;

PUTNAMES=YES;

label;

run;

EXPECTED OUTPUT:

id	var1	var2
sid	value1	value2
1	2	3
2	4	4
3	4	2
4	2	1
5	2	3
6	3	9

Re: export variable names and labels both in excel

RichardinOz — Fri, 16 Nov 2012 06:45:36 GMT

When I run your code I get

id,var1,var2

1,2,3

2,4,4

3,4,2

4,2,1

5,2,3

6,3,9

which is what I would expect.

Maybe you were expecting the labels to appear in addition to or instead of the variable names. All PUTNAMES does is write the variable names into the first row. To change these names, use the RENAME statement.

data test;
	input id var1 var2;
	rename id=sid var1=value1 var2=value2;
	cards;

01 2 3

02 4 4

03 4 2

04 2 1

05 2 3

06 3 9

;

Note that the CSV file format is a (somewhat semi) standard and it does not include the facility to have more than one header row. If you want a file in such a format you need to code it in a data step.

Richard in Oz

Message was edited by: Richard in Oz Added note.

Re: export variable names and labels both in excel

help09 — Fri, 16 Nov 2012 09:27:51 GMT

I want both(variable name & variable label) when i output dataset to excel as shown my expected output

Re: export variable names and labels both in excel

RichardinOz — Fri, 16 Nov 2012 11:21:01 GMT

Then you can't use proc export and instead you have to do some of the heavy lifting it does in the background. You can access a table containing the metadata (names and labels) and put them into some macro variables:

Proc SQL noprint ;

/* column names header row */

Select name

into :namelist

separated by ','

From dictionary.columns

Where libname = 'WORK'

And memname = 'TEST'

;

%Put &namelist ;

/* column labels header row */

Select label

into :labellist

separated by ','

From dictionary.columns

Where libname = 'WORK'

And memname = 'TEST'

;

%Put &labellist ;

/* variables for put statement */

Select name

into :putlist

separated by ' '

From dictionary.columns

Where libname = 'WORK'

And memname = 'TEST'

;

%Put &putlist ;

Quit ;

Then create the output file (notice I've added an extension so Excel will recognise it) :

data _null_;

set test;

file 'C:\temp\output.csv' delimiter=',' DSD DROPOVER LRECL=256;

if _n_=1

then

do;

put "&namelist";

put "&labellist";

end;

put &putlist ;

Run ;

The output.csv file contents are

id,var1,var2

sid,value1,value2

1,2,3

2,4,4

3,4,2

4,2,1

5,2,3

6,3,9

which, unless you've altered Excel defaults, will open as the table required.

Note that this is a simplified version of what proc export does behind the scenes and assumes that formats have already been assigned or that the defaults are satisfactory. BTW do not put WORK and TEST in lower case in the SQL.

Richard in Oz