export variable names and labels both in excel

[help09]

Hi

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

[RichardinOz]

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.

[help09]

Hi

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

[RichardinOz · Accepted solution]

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