topic Re: label many columns from _YY_MM_DD to YY/MM/DD in SAS Programming

label many columns from _YY_MM_DD to YY/MM/DD

Ronein — Mon, 23 Mar 2020 14:59:52 GMT

Hello

I have the following problem.

I am using many source data sets in order to create one summary data set.

In the summary data set there are many columns with names structure _YY_MM_DD .

I wish that I could give names with structure YY/MM/DD but in SAS it is not possible so I decided to give names with structure _YY_MM_DD.

I would like to use proc print in order to print the summary data set.

My question is how to label the culumns from structure _YY_MM_DD to YY/MM/DD?

Since there are many columns (300 columns) I am looking for a useful method to do it and not write it manually in label statement withing proc print.

Any idea?

Re: label many columns from _YY_MM_DD to YY/MM/DD

PaigeMiller — Mon, 23 Mar 2020 15:15:27 GMT

As I think about this problem, it seems to me that this is a case where a LONG data set would work much much much much much much much much much much much much much much much much much much much much much much much much better than a wide data set. On each row, you could have the actual SAS date (not _YY_MM_DD but a true SAS date value such as 21997, optionally formatted any way you would like) and whatever data you want for that date. Then PROC REPORT can produce the necessary YY/MM/DD column headings with very little programming effort, although I sincerely question the value of a PROC PRINT or PROC REPORT with 300 columns.

Important concept: your SAS data set does not have to be in the form you want it printed. It can be in a very different form, and then the reporting procedure (PROC PRINT, PROC TABULATE, PROC REPORT) can make it appear in the form you want.

Adding: PROC TRANSPOSE can turn your wide data set into the necessary long data set.

Re: label many columns from _YY_MM_DD to YY/MM/DD

ballardw — Mon, 23 Mar 2020 16:31:22 GMT

I agree with @PaigeMiller about the usefulness of 300 columns with that sort of label.

The basic approach is to get the names of the variables and the current label into a data set. Then you can use a data step with call execute to make a call to Proc Datasets to change the labels with the Modify statement.

A similar question with the key elements is answered here:

https://communities.sas.com/t5/SAS-Programming/How-do-I-create-labels-for-more-than-400-variables/m-p/254070

Re: label many columns from _YY_MM_DD to YY/MM/DD

PaigeMiller — Mon, 23 Mar 2020 16:36:41 GMT

But if you use PROC REPORT, you don't have to go through the programming effort to modify labels.

Re: label many columns from _YY_MM_DD to YY/MM/DD

Reeza — Mon, 23 Mar 2020 16:37:13 GMT

How did you get to that data set? Did you have it in a long format initially and then PROC TRANSPOSE flipped it?
If so, there's a method in that step to ensure you get the appropriate labels in a single step. I would recommend that approach to fix it as well since it's dynamic.

Re: label many columns from _YY_MM_DD to YY/MM/DD

Reeza — Mon, 23 Mar 2020 17:03:11 GMT

Here's two methods. The first uses PROC TRANSPOSE and the second uses PROC DATASETS.

*create dummy data;
data have;
	array _dates(31) _2020_01_01 - _2020_01_31;

	do ID=1 to 100;

		do i=1 to dim(_dates);
			_dates(i)=rand('integer', 0, 100);
		end;
		output;
	end;
	drop i;
run;

*transpose to long format;
proc transpose data=have out=_long;
	by ID;
run;

*add label string;
data _long2;
	set _long;
	*create label;
	label_str=input(compress(_name_, "_"), yymmdd8.);
	format label_str yymmdds10.;
run;

*transpose to wide format;
proc transpose data=_long2 out=wide (drop=_name_);
	by ID;
	id _name_;
	idlabel label_str;
	var col1;
run;

*display output;
proc print data=wide (obs=10) label;
run;

This one is better if you have large data set and want to make changes in place. This does not manipulate the data in any manner, it adds the labels to the data set metadata instead and is ready to use when you use the data set.

data _null_;
	*set input data, just one observation;
	set have (obs=1);
	*set list of dates to add labels to;
	array _dates(*) _:;
	length label_statement $30.;
	*start proc data sets, have is input data modify here if necessary;
	call execute('proc datasets lib=work nodetails nolist; modify have; label');

	do i=1 to dim(_dates);
		*name of variable;
		str1=vname(_dates(i));
		*label for date;
		str_label=put(input(compress(str1, "_"), yymmdd8.), yymmdds10.);
		*create string for relabel eg _2020_01_30 = "2020/01/30";
		label_statement=catt(str1, '=', quote(str_label));
		*run the command;
		call execute(label_statement);
	end;
	*close proc datasets;
	call execute(";run;quit;");
run;

*display output;

proc print data=have (obs=10) label;
run;

@Ronein wrote:

Hello

I have the following problem.

I am using many source data sets in order to create one summary data set.

In the summary data set there are many columns with names structure _YY_MM_DD .

I wish that I could give names with structure YY/MM/DD but in SAS it is not possible so I decided to give names with structure _YY_MM_DD.

I would like to use proc print in order to print the summary data set.

My question is how to label the culumns from structure _YY_MM_DD to YY/MM/DD?

Since there are many columns (300 columns) I am looking for a useful method to do it and not write it manually in label statement withing proc print.

Any idea?

Re: label many columns from _YY_MM_DD to YY/MM/DD

ballardw — Mon, 23 Mar 2020 18:46:17 GMT

@PaigeMiller wrote:

But if you use PROC REPORT, you don't have to go through the programming effort to modify labels.

Agreed, or at least not more than one.

But since OP has not provided an example of the data structure he currently has or exactly what the output should be a bit of shooting in the dark.

Re: label many columns from _YY_MM_DD to YY/MM/DD

Ksharp — Tue, 24 Mar 2020 11:11:05 GMT

data have;
input id _20_01_01 _20_01_02;
cards;
1 2 3
2 4 6
;
proc transpose data=have(obs=0) out=temp;
var _all_;
run;
data temp;
 set temp;
 if _name_ =: '_' then label=translate(substr(_name_,2),'/','_');
  else label=_name_;
run;
data _null_;
 set temp end=last;
 if _n_=1 then 
call execute('proc datasets library=work nolist nodetails;modify have;label ');
call execute(cats(_name_,'="',label,'"'));
if last then call execute(';quit;');
run;

Re: label many columns from _YY_MM_DD to YY/MM/DD

Tom — Tue, 24 Mar 2020 12:35:31 GMT

Are you talking about the labels attached to the variables or the names of the variables? You can use any text you want for the labels.

Re: label many columns from _YY_MM_DD to YY/MM/DD

Patrick — Tue, 24 Mar 2020 12:47:10 GMT

As others already pointed out it's likely best to revisit the process which creates this data structure and variable names in first place.

But to just provide a solution for how I understand your question below code adds labels to variable which follow the naming convention of _yy_mm_dd

data work.have;
  array vars {*} 8 _20_01_01 - _20_01_20 (20*1);
  output;
  stop;
run;

%macro _addLable(ds);
  %let ds=%upcase(&ds);
  %local _lref _tbl;
  %let _lref=%scan(WORK.&ds,-2);
  %let _tbl=%scan(&ds,-1);
  proc sql;
    create view _v_varlist as
    select name, input(compress(name,,'kd'),yymmdd6.) as name_dt
    from dictionary.columns
    where libname="&_lref" and memname="&_tbl"
      and prxmatch('/^_\d\d_\d\d_\d\d$/oi',strip(name));
    ;
  quit;

  filename codegen temp;
  data _null_;
  /*  file print;*/
    file codegen;
    set _v_varlist end=last;
    if _n_=1 then 
      do;
        put
          "proc datasets lib=&_lref nolist nowarn;" /
          "  modify &_tbl;" /
          '    label'
          ;
      end;
        put @6 name "='" name_dt yymmdds10. "'";
      if last then
        do;
          put 
            @7 ';' /
            @3 'run;' /
            @1 'quit;'
            ;
        end;
      ;
  run;

  %include codegen / source2;
  filename codegen clear;
%mend;

%_addLable(have)

proc print data=have label;
run;