Set the labels of a SAS Dataset equal to their var

问题:

I'm working with a rather large several dataset that are provided to me as a CSV files. When I attempt to import one of the files the data will come in fine but, the number of variables in the file is too large for SAS, so it stops reading the variable names and starts assigning them sequential numbers. In order to maintain the variable names off of the data set I read in the file with the data row starting on 1 so it did not read the first row as variable names -

proc import file="X:\xxx\xxx\xxx\Extract\Live\Live.xlsx" out=raw_names dbms=xlsx replace;
    SHEET="live";
    GETNAMES=no;
    DATAROW=1;
run;

I then run a macro to start breaking down the dataset and rename the variables based on the first observations in each variable -

%macro raw_sas_datasets(lib,output,start,end);
    data raw_names2;
        raw_names;
            if _n_ ne 1 then delete;
            keep A -- E &start. -- &end.;
    run;
    proc transpose data=raw_names2 out=raw_names2;
        var A -- &end.;
    run;
    data raw_names2;
        set raw_names2;
            col1=compress(col1);
    run;
    data raw_values;
        set raw;
            keep A -- E &start. -- &end.;
    run;
    %macro rename(old,new);
        data raw_values;
            set raw_values;
                rename &old.=&new.;
        run;
    %mend rename;
    data _null_;
        set raw_names2;
            call execute('%rename('||_name_||","||col1||")");
    run;
    %macro freq(var);
        proc freq data=raw_values noprint;
           tables &var. / out=&var.;
        run;
    %mend freq;
     data raw_names3;
        set raw_names2;
            if _n_ < 6 then delete;
     run;
    data _null_;
        set raw_names3;
           call execute('%freq('||col1||")");
    run;
    proc sort data=raw_values;
        by StudySubjectID;
    run;
    data &lib..&output.;
        set raw_values;
    run;
%mend raw_sas_datasets;

The problem I'm running into is that the variable names are now all set properly and the data is lined up correctly, but the labels are still the original SAS assigned sequential numbers. Is there any way to set all of the labels equal to the variable names?

回答1:

If you just want to remove the variable labels (at which point they default to the variable name), that's easy. From the SAS Documentation:

proc datasets lib=&lib.;
  modify &output.;
  attrib _all_ label=' ';
run;

I suspect you have a simpler solution than the above, though.

• The actual renaming step needs to be done differently. Right now it's rewriting the entire dataset over and over again - for a lot of variables that is a terrible idea. Get your rename statements all into one datastep, or into a PROC DATASETS, or something else. Look up 'list processing SAS' for details on how to do that; on this site or on google you will find lots of solutions.

• You likely can get SAS to read in the whole first line. The number of variables isn't the problem; it is probably the length of the line. There's another question that I'll find if I can on this site from a few months ago that deals with this exact problem.

• My preferred option is not to use PROC IMPORT for CSVs anyway; I would suggest writing a metadata table that stores the variable names and the length/types for the variables, then using that to write import code. A little more work at first, but only has to be done once per study and you guarantee PROC IMPORT isn't making silly decisions for you.

回答2:

In the library sashelp is a table vcolumn. vcolumn contains all the names of your variables for each library by table. You could write a macro that puts all your variable names into macro variables and then from there set the label.

Here's some code that I put together (not very pretty) but it does what you're looking for:

data test.label_var;
x=1;
y=1;
label x = 'xx';
label y = 'yy';
run;

proc sql noprint;
     select count(*) into: cnt
            from sashelp.vcolumn
                 where memname = 'LABEL_VAR';quit;
%let cnt = &cnt;

proc sql noprint;
     select name into: name1 - :name&cnt
            from sashelp.vcolumn
                 where memname = 'LABEL_VAR';quit;

%macro test;

   %do i = 1 %to &cnt;

       proc datasets library=test nolist;

            modify label_var;

            label &&name&i=&&name&i;

       quit;

  %end;
%mend test;
%test;