topic Re: Change few column name from another file in SAS Programming

Change few column name from another file

ashish112 — Mon, 26 Nov 2018 13:12:07 GMT

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.

Re: Change few column name from another file

PaigeMiller — Mon, 26 Nov 2018 13:21:33 GMT

/* 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;

Re: Change few column name from another file

ashish112 — Mon, 26 Nov 2018 14:17:55 GMT

syntax error comes for cats function

Re: Change few column name from another file

RW9 — Mon, 26 Nov 2018 14:57:15 GMT

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.

Re: Change few column name from another file

ashish112 — Mon, 26 Nov 2018 16:14:32 GMT

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, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,

Re: Change few column name from another file

RW9 — Mon, 26 Nov 2018 16:24:13 GMT

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.

Re: Change few column name from another file

ashish112 — Mon, 26 Nov 2018 16:36:56 GMT

sas log attached here

Re: Change few column name from another file

RW9 — Mon, 26 Nov 2018 16:41:31 GMT

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

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

Re: Change few column name from another file

PaigeMiller — Mon, 26 Nov 2018 16:44:37 GMT

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

My bad

Re: Change few column name from another file

ashish112 — Mon, 26 Nov 2018 17:02:41 GMT

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?

Re: Change few column name from another file

FreelanceReinh — Mon, 26 Nov 2018 18:27:59 GMT

Omit the libref (work) in the MODIFY statement:

modify import;

This is general syntax of PROC DATASETS.

Re: Change few column name from another file

ashish112 — Tue, 27 Nov 2018 04:47:51 GMT

Hi,

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

Re: Change few column name from another file

ashish112 — Tue, 27 Nov 2018 08:52:23 GMT

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?

Re: Change few column name from another file

RW9 — Tue, 27 Nov 2018 08:57:30 GMT

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.

Re: Change few column name from another file

ashish112 — Tue, 27 Nov 2018 09:56:37 GMT

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.

Re: Change few column name from another file

FreelanceReinh — Tue, 27 Nov 2018 10:11:44 GMT

@ashish112: Add the statement

guessingrows=max;

to the PROC IMPORT step for data2.csv to avoid this truncation.

In addition, you will need the REPLACE option of the PROC IMPORT statement.

Re: Change few column name from another file

ashish112 — Tue, 27 Nov 2018 10:38:26 GMT

I have tried this still same error comes, see the attached log

Re: Change few column name from another file

RW9 — Tue, 27 Nov 2018 11:17:07 GMT

2171 proc sql noprint;

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

2173 where upcase(oldname) in (select name from sashelp.vcolumn where libname="WORK" and memname="import3");

NOTE: No rows were selected.

2174 quit;

The above is not good, you note the "No rows were selected", that means nothing is created. Probably you meant:

and memname="IMPORT3"

Note, the string must match what is in the dataset sashelp.vcolumn!

Also, where did import3 come in?

Re: Change few column name from another file

ashish112 — Tue, 27 Nov 2018 11:28:10 GMT

I have changed the dataset name for "data1.csv" to work.import3

Re: Change few column name from another file

ashish112 — Tue, 27 Nov 2018 14:01:24 GMT

I am still working on solution, no success yet. My major concern is the error comes when there is extra variables in data2 file which are not in data1 file.

Is there any other solution?