topic Re: How to display variable label in first row and variable name in second row of a dataset in SAS Programming

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

abraham1 — Wed, 14 Sep 2022 18:33:34 GMT

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;

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

PeterClemmensen — Wed, 14 Sep 2022 18:36:49 GMT

Do you want this as a SAS data set?

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

Tom — Wed, 14 Sep 2022 18:59:26 GMT

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

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

Reeza — Wed, 14 Sep 2022 19:19:11 GMT

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;

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

ballardw — Wed, 14 Sep 2022 19:51:47 GMT

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;

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

abraham1 — Thu, 15 Sep 2022 05:47:35 GMT

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

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

andreas_lds — Thu, 15 Sep 2022 11:53:08 GMT

@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.

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

Ksharp — Thu, 15 Sep 2022 12:04:10 GMT

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;

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

Tom — Thu, 15 Sep 2022 13:44:59 GMT

@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.

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

Ksharp — Thu, 15 Sep 2022 14:02:09 GMT

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;