export variable names and labels both in excel

Accepted Solution Solved
Reply
Occasional Contributor krc
Occasional Contributor
Posts: 17
Accepted Solution

export variable names and labels both in excel

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:

idvar1var2
sid value1 value2
123
244
342
421
523
639

Accepted Solutions
Solution
‎11-16-2012 06:21 AM
Super Contributor
Posts: 644

Re: export variable names and labels both in excel

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 Smiley Tongueutlist

            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

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: export variable names and labels both in excel

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.

Occasional Contributor krc
Occasional Contributor
Posts: 17

Re: export variable names and labels both in excel

Posted in reply to RichardinOz

Hi

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

Solution
‎11-16-2012 06:21 AM
Super Contributor
Posts: 644

Re: export variable names and labels both in excel

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 Smiley Tongueutlist

            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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 6164 views
  • 0 likes
  • 2 in conversation