SAS Put Certain Row as New Variable Names After Manipulation

1
votes

After importing my CSV data with GETNAMES=NO, I have 59 columns with variable names VAR1, VAR2, . . . VAR59. My first row contains the names I need for the new variables, but they first needed manipulated by removing special characters and turning spaces into underscores since SAS doesn't like spaces in variable names. This is the array I used for that piece:

DATA DATA1; SET DATA (FIRSTOBS=7);
ARRAY VAR(59) VAR1-VAR59;
IF _N_ = 1 THEN DO;
DO I = 1 TO 59;
    VAR[I] = COMPRESS(TRANSLATE(TRIM(VAR[I]),'_',' '),'?()');
    PUT VAR[I]=;
    END;
END;
DROP I;
RUN;

This worked perfectly, but now I need to get this first row up to the new variable names. I tried a similar array to perform this:

DATA DATA2; SET DATA1;
ARRAY V(59) VAR1-VAR59;
DO I = 1 TO 59;
    IF _N_ = 1 AND V[I] NE "" THEN CALL SYMPUT("NEWNAME",V[I]);
    RENAME VAR[I] = &NEWNAME;
    END;
    DROP I;
RUN;

This only puts the name of VAR59 since there is no [i] connected to the &NEWNAME, and it still isn't working quite right. Any suggestions to moving a row up to variable names AFTER manipulation?

2
arrayssas
SAS will happily convert those variable names for you in roughly the fashion you are describing (I think it would turn the special characters also into underscores, but otherwise the same). Is there a reason you don't want to let SAS do it automatically? Or is it not (are you in Enterprise Guide for example)? – Joe
I'd love for SAS to do this automatically -- I guess I initially overlooked it and went straight into manipulating the row names I wanted to keep consistent with other data we have. I appreciate the insight. – kstats9pt3

2 Answers

1
votes

Your primary problem is you are trying to use a macro variable in the data step it's created in. You can't. You're also trying to create rename statements in the data step; rename, as with other similar statements (keep, drop), must be defined before the data step is compiled.

You need to write code somewhere - either in a text file, a macro variable, whatever - with this information. For example:

filename renamef temp;
data _null_;
  set myfile (obs=1);
  file renamef;
  array var[59];
  do _i = 1 to dim(Var);
    [your code to clean it out];
    strput = cat("rename",vname(var[_i]),'=',var[_i],';');
    put strput;
  end;
run;
data want;
  set myfile (firstobs=2);
  %include renamef;
run;

There are lots of other examples to this on the site and on the web, "list processing" is the term for this.

1
votes

Joe -- using your suggestions and another one of your posts, the following worked flawlessly:

Put the row of needed variables into long format (in my case, first row so n = 1)

DATA NEWVARS; SET DATA;
    IF _N_ = 1 THEN OUTPUT NEWVARS;
RUN;

PROC TRANSPOSE DATA = NEWVARS OUT=NEWVARS1;
    VAR _ALL_;
RUN;

Create a list of rename macro calls.

PROC SQL;
    SELECT CATS('%RENAME(VAR=',_NAME_,',NEWVAR=',COL1,')')
    INTO :RENAMELIST SEPARATED BY ' '
    FROM NEWVARS1;
QUIT;

%MACRO RENAME(VAR=,NEWVAR=);
    RENAME &VAR.=&NEWVAR.;
%MEND RENAME;

Call in the list created in Step 2 to rename all variables.

PROC DATASETS LIB=WORK NOLIST;
    MODIFY DATA;
    &RENAMELIST.;
QUIT;

I had to perform a few additional checks making sure that the variable names were not greater than 32 characters, and this was easy to check for when the data was in long format after transposing. If there are certain words that make the lengths too long, a TRANWRD statement can easily replace them with abbreviations.