Change few column name from another file

[ashish112]

Hello Experts,

I am working on some SAS application, I have stuck in between where I have to change few column names from another file. 

In detail: I have attached two csv file here, Data1 and Data2.  I want to change few column names of Data1 file. The new name is available in Data2 file, column name "new name". I want to replace the old name to new name in Data1 file.  Need your help. It might be easy but I am new to SAS, so for me its difficult.

[PaigeMiller]

/* UNTESTED CODE */
proc import datafile='data1.csv' dbms=csv out=data1;
run;

proc import datafile='data2.csv' dbms=csv out=_names_;
run;

proc sql noprint;
    select cats(old_name,'=',new_name) separated by ' ' into :renames from _names_;
quit;

proc datasets library=work nolist;
    modify data1;
    change &renames;
run; quit;

[ashish112]

syntax error comes for cats function

[RW9]

And this syntax error you have posted where?  We cannot guess these things, please post all information necessary, what code are you running, test data, log output etc.

[ashish112]

proc sql print;
8186 select cats(evntkey,'=',code) separated by ' ' into:renames from conc;
_________
22
202
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,

[RW9]

That is not the log.  There is something which is happening before the 8186 line - the fact the proc sql print; code line does not have a number before it is an indication that something else has happened.  Please post full log, or full code with test data.

[ashish112]

sas log attached here

[RW9]

Ah yes, separated should appear after into, e.g.

proc sql print;
  select cats(name," ",sex) into :test separated by " " from sashelp.class;
quit;

[PaigeMiller]

select cats(oldname,'=',newname) into :renames separated by ' ' from work.import1;

My bad 

[ashish112]

Hi, thank you from quick response.

There is still one error, I have attached the log here. I am using sas university edition, do this make difference?

[FreelanceReinh]

Omit the libref (work) in the MODIFY statement:

modify import;

This is general syntax of PROC DATASETS.

[ashish112]

Hi,

I also tried this but there are multiple errors, I have attached the log here.

[ashish112]

I got this , we have to use "rename" instead of "Change". 

this code is working:

proc sql noprint;
select cats(oldname,'=',newname) into :renames separated by ' ' from import1;
quit;

proc datasets library= work nolist;
modify import;
rename &renames;
run;
quit;

there is one more issue. If there is extra variables in data2 which is not in data 1 then it throws error. like,

ERROR: Variable AENF7 is not on file WORK.IMPORT.

ERROR: Variable BENF2 is not on file WORK.IMPORT.

the variables AENF7 BENF2 are not in data1 but available in data2

Is there any way to solve this?

[RW9]

Then you will need to cross reference the sashelp metadata something like:

proc sql noprint;
  select cats(oldname,'=',newname) 
  into   :renames separated by ' ' 
  from   import1
  where  upcase(oldname) in (select name from sashelp.vcolumn where libname="WORK" and memname="IMPORT2");
quit;

proc datasets library= work nolist;
modify import;
rename &renames;
run;
quit;

Sashelp.vcolumns is the column metadata for all libraries in existence, so you can pull a list by providing the libname and dataset (normally uppercase) and then seeing if the variable is in that list.

What I would however say at this point is why do you have such a mess?  First off having the metdata in a seperate file to read is ok, but not ideal, but if that metadata doesn't even match the data what is the point?  A data transfer comprises of two parts one more important than the other.  The important part is the documentation containing a description of the data - completely and accurately. Without this data transfer is a failure.   

[ashish112]

Hi, thanks for the help.

 I notice that there is still some weird errors, when we have variables like

AENF1

AENF2

.

AENF10

AENF11

then these codes not read AENF10 and AENF11, Its only read upto 5 character only and the error throws for more than 5 char variables.

I have attached the sas log here.