BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
help09
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
RichardinOz
Quartz | Level 8

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

View solution in original post

3 REPLIES 3
RichardinOz
Quartz | Level 8

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
Fluorite | Level 6

Hi

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

RichardinOz
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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