topic Re: From imported CSV, keep variable names and apply labels from CSV cell in SAS Programming

From imported CSV, keep variable names and apply labels from CSV cell

cedpme — Thu, 09 Mar 2023 21:28:27 GMT

Hello all,

I am using SAS EG 8.3.

Scenario: assuming you have a table and you generate the proc contents from this table, you would get something like this (I created a dummy table called "TESTING_FIELDNAMES_LABELNAMES1" then exported to Microsoft Excel, keeping pertinent fields that I need):

PROC CONTENTS Export:

I highlighted the field "LABEL", this where I need your help

Here is a mockup of the labels I would modify in Excel, hoping I can import it later to modify my table with updated labels:

I know I can use the LABEL function in a SAS Datastep to add my labels, as follows:

Which will update my labels as follows:

Reason: I have built several data marts that are sources of truth that many departments use. The issue I have is that I constantly get question asking for field sourcing or how a calculated field is created. My hope is that I can maintain an the extracted PROC CONTENTS workbook for each data asset, update the labels as necessary, then import the updated workbook back into the project giving me sudo "dynamic labels" that are easy to maintain by my team.

Thank you!

Re: From imported CSV, keep variable names and apply labels from CSV cell

Kurt_Bremser — Thu, 09 Mar 2023 21:37:03 GMT

Create a dataset with library, memname, variable name and label text (use a DATA step with DATALINES), from which you can create code for PROC DATASETS with CALL EXECUTE.

Re: From imported CSV, keep variable names and apply labels from CSV cell

Tom — Thu, 09 Mar 2023 23:00:56 GMT

There is no function named LABEL. What you showed is the LABEL statement.

You will need to use code generation to convert your data into a label statement.

If the set of variables is small you can just put the code into a single macro variable.

proc sql noprint;
select catx('=',nliteral(name),quote(trim(label))
  into :labels separated by ' '
  from HAVE 
;
quit;

Which you can then use as part of a LABEL statement.

LABEL &labels ;

But remember that a macro variable can only hold 64K bytes.

So if you have too much to put into one macro variable just write the code to a file instead.

filename code temp;
data _null_;
  set have end=eof;
  file code ;
  if _n_=1 then put 'label' ;
  nname=nliteral(name);
  put nname '=' label :$quote. ;
  if eof then put ';' ;
run;

If you are running a data step to create the dataset then just add this label statement to that step.

data pretty ;
  set ugly ;
  %include code / source2;
run;

Or if you already have the dataset and you want to change the labels then use PROC DATASETS instead. So to add the labels to MYLIB.PRETTY use code like:

proc datasets lib=MYLIB nolist;
  modify pretty ;
%include code / source2;
  run;
quit;

Re: From imported CSV, keep variable names and apply labels from CSV cell

cedpme — Fri, 10 Mar 2023 14:54:25 GMT

Tom, thank you for taking the time out of your day to help me out. I truly appreciate the detail and examples you shared; I was able to follow along and make it work on my end. Thank you again!

Re: From imported CSV, keep variable names and apply labels from CSV cell

cedpme — Fri, 10 Mar 2023 14:58:07 GMT

Kurt, thank you for your reply; unfortunately I am not as familiar with SAS as you are. I understood what you shared, but without examples or code to test on my end, I wasn't able to get the results I needed. I am sure others will be able to grasp the concepts you share and quickly get a solution; nonetheless, thank you for helping me out.