topic Re: Help to rename in data infile in SAS Programming

Help to rename in data infile

Sk1_SAS — Tue, 21 May 2019 16:17:29 GMT

Hi Team,

every day i receive a .text file with different variable, so i need to identify the variables and then rename, i use this code to discover the variables:

DATA COLUMN(KEEP=COL NAME)

;

INFILE .txtfile DSD DLM=';' COLUMN=CC TERMSTR=CRLF LENGTH=LL lrecl=999999 TRUNCOVER;

IF _N_=1 THEN DO ;

INPUT @;

DO COL=1 BY 1 WHILE (CC <= LL) ;

LENGTH NAME$350 ;

INPUT NAME @;

OUTPUT COLUMN;

END;

INPUT;

END;

ROW+1;

RUN;

after that, i got the name of the all variables that i identified, because i have a table with the new names.

PROC SQL;

CREATE TABLE COLUMN_V2

AS SELECT

A.NAME,

B.NEW_NAME

FROM COLUMN AS A

LEFT JOIN TABLE_NEW_NAMES AS B

ON A.NAME=B.NAME;

QUIT;

Now i need to import the file renaming the fields using a macro, do you know how i can do that? im asking for a macro, because this process needs to be automatic.

Thanks a lot!!

Re: Help to rename in data infile

Reeza — Tue, 21 May 2019 16:51:22 GMT

Here's an example that uses PROC DATASETS - which is faster than recreating your data.

In this example, the name change is dynamic and assumes you have a data set with the old and new names.

If you have a lot of variables (thousands) this may not work because there's a limit of 64K characters within a macro variable.

data sample;
do i=10000 to 12000;
	start_date=i;
	middle_date=i+3;
	end_date=i+5;
	date_no_change=start_date;
	output;
end;
format start_date end_date middle_date date9.;
run;

*data set with old and new names;
data rename_list;
infile cards dlm=',' truncover;
length name new_name $32.;
input name $ new_name $;
cards;
start_date, my_start_date
middle_date, my_mid_date
end_date, my_end_date
;;;;


proc sql noprint;
select catx("=", name, new_name) 
into :rename_list
separated by " "
from renameTable;
quit;


%put &rename_list;

proc datasets library=work nodetails nolist;
modify sample;
rename &rename_list;
run; quit;

proc print data=sample noobs;
run;d

@Sk1_SAS wrote:

Hi Team,

every day i receive a .text file with different variable, so i need to identify the variables and then rename, i use this code to discover the variables:

DATA COLUMN(KEEP=COL NAME)

;

INFILE .txtfile DSD DLM=';' COLUMN=CC TERMSTR=CRLF LENGTH=LL lrecl=999999 TRUNCOVER;

IF _N_=1 THEN DO ;

INPUT @;

DO COL=1 BY 1 WHILE (CC <= LL) ;

LENGTH NAME$350 ;

INPUT NAME @;

OUTPUT COLUMN;

END;

INPUT;

END;

ROW+1;

RUN;

after that, i got the name of the all variables that i identified, because i have a table with the new names.

PROC SQL;

CREATE TABLE COLUMN_V2

AS SELECT

A.NAME,

B.NEW_NAME

FROM COLUMN AS A

LEFT JOIN TABLE_NEW_NAMES AS B

ON A.NAME=B.NAME;

QUIT;

Now i need to import the file renaming the fields using a macro, do you know how i can do that? im asking for a macro, because this process needs to be automatic.

Thanks a lot!!

Re: Help to rename in data infile

Tom — Tue, 21 May 2019 17:33:05 GMT

How many columns do your files have? If it is fewer then about 1000 you can probably get away with just using a macro variable to contain the RENAME list.

First get the names from the file.

filename newfile "newfile_2019_05_21.txt" termstr=crlf ;

data names ;
  infile newfile  dsd dlm=';' obs=1 ;
  varnum + 1;
  input name :$350. @@;
run;

Then you can join that list to your master list and find out which ones need to be renamed and put the list of OLD=NEW pairs into a macro variable.

proc sql noprint;
%let renames=;
select catx(a.name,b.new_name) into :renames separated by ' '
  from names a inner join table_new_names b
  on a.name = b.name
  where a.name ne b.new_name
    and not missing(b.new_name)
;
quit;

Then you can use that list in a RENAME= dataset option.

proc import file=newfile replace dbms=dlm 
   out=want(rename=(&renames)) 
;
  delimiter=';';
run;

Are your names really up to 350 characters long? Are you using PROC IMPORT to read the file? If both then perhaps you should skip the headers and just let PROC IMPORT create the variables using generic names VAR1,VAR2,.... and rename those.

proc sql noprint;
%let renames=;
select catx(cats('VAR',a.varnum),coalesce(b.new_name,substr(a.name,1,32)))
   into :renames separated by ' '
  from names a left join table_new_names b
  on a.name = b.name
;
quit;
proc import file=newfile replace dbms=dlm 
   out=want(rename=(&renames)) 
;
  delimiter=';';
  datarow=2;
  getnames=no;
run;

Re: Help to rename in data infile

Sk1_SAS — Tue, 21 May 2019 18:52:45 GMT

Hi Tom,

Tks a lot for yout help, i dont know the number of columns, some days are 300 other days are 1200, its not a fixed base.

I execute your code "proc import file=newfile replace dbms=dlm
out=want(rename=(&renames))
; delimiter=';';
run;"

and this error appears

ERROR 79-322: Expecting a =.

Tks!!!

Re: Help to rename in data infile

Tom — Tue, 21 May 2019 19:07:41 GMT

@Sk1_SAS wrote:

Hi Tom,

Tks a lot for yout help, i dont know the number of columns, some days are 300 other days are 1200, its not a fixed base.

I execute your code "proc import file=newfile replace dbms=dlm
out=want(rename=(&renames))
; delimiter=';';
run;"

and this error appears

ERROR 79-322: Expecting a =.

Tks!!!

Show the value of the RENAMES macro variable that got created. It is possible your column headers in your source file are not valid SAS variable names. You might get away with using the NLITERAL() function to convert them if your sessions is using VALIDVARNAME=ANY option.

If the names are too long then perhaps the value got truncated?

If you need to generate more rename pairs than can fit into a macro variable then generate the code another way.

For example you could create a file with a RENAME statement. Say you a dataset named RENAMES with the NAME and NEW_NAME pairs you could generate a temporary file with the rename statement and then use %INCLUDE to add that statement to a PROC DATASETS step to change the names in the file you created using PROC IMPORT.

filename rename temp;
data _null_;
  set renames end=eof;
  file rename lrecl=80;
  if _n_= then put 'rename ' @;
  put name '=' new_name @;
  if eof then put / ';' ;
run;
proc datasets nolist lib=work;
modify want ;
%include rename ;
run;
quit;