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.
Look, we cannot guess what is happening your end. Please post test data - which gives actual examples of what you are working with - for both datasets, in the form of a datastep. Then show the code you are running. Then show the log that it is created. The code I presented above works fine given the parameters know by me - that person not sitting at your desk looking at your work:
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;
/* 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;
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.
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.
sas log attached here
Ah yes, separated should appear after into, e.g.
proc sql print; select cats(name," ",sex) into :test separated by " " from sashelp.class; quit;
select cats(oldname,'=',newname) into :renames separated by ' ' from work.import1;
My bad
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?
Omit the libref (work) in the MODIFY statement:
modify import;
This is general syntax of PROC DATASETS.
Hi,
I also tried this but there are multiple errors, I have attached the log here.
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,
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.