How to display variable label in first row and variable name in second row of a dataset

[abraham1]

Hi All,

From the test1 dataset, I want to display output like below . It means all variable label should display in first row where as the variable name as second row followed by dataset records.

Output

Patient ID  Patient age  Sex       Race    Event 2

pid              age             gender  race      event2

101                21              1           1           fever
102                21               2           1          fever
103                 31              1           1          fever
104                 43               2          1           fever

data test;
input pid age gender race event2 $;
cards;
101 21 1 1 fever
102 21 2 1 fever
103 31 1 1 fever
104 43 2 1 fever
;
run;
data test1;
set test;
label pid = 'Patient ID'
age = 'Patient age'
gender='Sex'
race = 'Race'
event2='Event 2'
;
run;

[PeterClemmensen]

Do you want this as a SAS data set?

[Tom · Accepted solution]

Do you mean you want a report like this?

data test;
  input pid age gender race event2 $;
  label
    pid = 'Patient ID'
    age = 'Patient age'
    gender='Sex'
    race = 'Race'
    event2='Event 2'
  ;
cards;
101 21 1 1 fever
102 21 2 1 fever
103 31 1 1 fever
104 43 2 1 fever
;

proc contents data=test noprint out=contents; run;

proc sql noprint;
  select catx('=',nliteral(name),quote(cats(coalesce(label,name),'/',name),"'"))
    into :labels separated by ' '
    from contents
  ;
quit;

proc print data=test split='/' noobs;
  label &labels ;
run;

Patient ID    Patient age      Sex     Race    Event 2
    pid           age        gender    race    event2

    101            21           1        1      fever
    102            21           2        1      fever
    103            31           1        1      fever
    104            43           2        1      fever

[ballardw]

Or since the OP already has a label statement modify it a bit to use the Split option:

proc print data=test noobs label split='*';
   label 
      pid = 'Patient ID*Pid'
      age = 'Patient age*Age'
      gender='Sex*Gender'
      race = 'Race*Race'
      event2='Event 2*Event2'
   ;   
run;

[abraham1]

Thank you so much Tom and Reeza for the solution.

In stead of using proc print, I want to store this output in a dataset where the label name present in first row and variable name in second row. I tried but both variable label and name print on the single row.

Its not working. Could you please help me once. 

Output like below

Patient ID	Patient age	Sex	Race	Event 2
pid	age	gender	race	event2
101	21	1	1	fever
102	21	2	1	fever
103	31	1	1	fever
104	43	2	1	fever

[andreas_lds]

---

@abraham1 wrote:

Thank you so much Tom and Reeza for the solution.

In stead of using proc print, I want to store this output in a dataset where the label name present in first row and variable name in second row. I tried but both variable label and name print on the single row.

Its not working. Could you please help me once. 

Output like below

Patient ID	Patient age	Sex	Race	Event 2
pid	age	gender	race	event2
101	21	1	1	fever
102	21	2	1	fever
103	31	1	1	fever
104	43	2	1	fever

---

This is not possible. The header of a dataset is limited to a single row. You could extend the labels so that the have both information (already suggested) and activate the option to display labels instead of names.

[Tom]

---

@abraham1 wrote:

Thank you so much Tom and Reeza for the solution.

In stead of using proc print, I want to store this output in a dataset where the label name present in first row and variable name in second row. I tried but both variable label and name print on the single row.

Its not working. Could you please help me once. 

Output like below

Patient ID	Patient age	Sex	Race	Event 2
pid	age	gender	race	event2
101	21	1	1	fever
102	21	2	1	fever
103	31	1	1	fever
104	43	2	1	fever

---

Since a DATASET has only one data type per variable if you store that information in a dataset then all of the variables will have to be character.  So you will need to convert the numbers like 21 in your table there into strings also.  Plus what variable names would you like to use for the variables in this new dataset?  Since SAS labels can have up to 256 characters they are too long to use a variable names.

[Reeza]

For CSV/Text files this works:

/*This is an example of how to export a data set with two header rows, 
one that is labels and oen that is the variable names
*/

*Create demo data;
data class;
	set sashelp.class;
	label age='Age, Years' weight = 'Weight(lbs)' height='Height, inches';
run;

proc sql noprint;
create table temp as
select name as _name_, label as _label_
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
select cats(quote(name),"n") into :varList separated by ' '
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
quit;

data _null_;
file "&sasforum.\datasets\TwoLinesHeader.csv" dsd;
set class;
if _n_ = 1 then do;
do until(eof);
    set temp end=eof;
    put _name_ @;
    end;
put;
eof = 0;
do until(eof);
    set temp end=eof;
    put _label_ @;
    end;
put;
end;

put (&varList) (:);
run;

[Ksharp]

This question has already been discussed before .

data test;
  input pid age gender race event2 $;
  label
    pid = 'Patient ID'
    age = 'Patient age'
    gender='Sex'
    race = 'Race'
    event2='Event 2'
  ;
cards;
101 21 1 1 fever
102 21 2 1 fever
103 31 1 1 fever
104 43 2 1 fever
;

proc contents data=test noprint out=contents; run;

proc sql noprint;
  select catx(' ','("',label,'" ',name,')')
    into :labels separated by ' '
    from contents
  ;
quit;

%put &labels;

proc report data=test nowd;
columns &labels.;
define _all_/display ;
attrib _all_ label=' ';
run;

[Ksharp]

data test;
  input pid age gender race event2 $;
  label
    pid = 'Patient ID'
    age = 'Patient age'
    gender='Sex'
    race = 'Race'
    event2='Event 2'
  ;
cards;
101 21 1 1 fever
102 21 2 1 fever
103 31 1 1 fever
104 43 2 1 fever
;

proc contents data=test noprint out=contents; run;

proc sql noprint;
  select catx(' ','("',label,'" ',name,')')
    into :labels separated by ' '
    from contents
  ;
quit;

%put &labels;
ods excel file='c:\temp\want.xlsx';
proc report data=test nowd;
columns &labels.;
define _all_/display ;
attrib _all_ label=' ';
run;
ods excel close;
proc import datafile='c:\temp\want.xlsx' out=want dbms=xlsx replace;
getnames=no;
run;