Change few column name from another file

[FreelanceReinh]

@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.

[ashish112]

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

[RW9]

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?

[ashish112]

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

[ashish112]

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?

[RW9 · Accepted solution]

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;

[ashish112]

Hi, Yeah I know its needed more data for you. I have attached sas codes,
sas log and two data sets here. I hope it helps.

[FreelanceReinh]

@FreelanceReinh wrote:

@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.

---

You forgot the GUESSINGROWS statement.

[FreelanceReinh]

I was about to post the following PROC SQL step when I saw RW9's earlier post using an equivalent technique (sashelp.vcolumn rather than dictionary.columns😞

proc sql noprint;
    select cats(old_name,'=',new_name) into :renames separated by ' ' from 
    (select * from _names_ a, dictionary.columns b
     where b.libname='WORK' & b.memname='DATA1' & a.old_name=b.name);
quit;

(where _names_ and DATA1 are the datasets created from your original Data2.csv and Data1.csv files via PROC IMPORT; note the underscores in the variable names).

[ashish112]

Hi , thanks for the suggestion.

I tried this but error comes at line:  rename &renames; 

I have attached the log here, can you please look in to this? sorry for late reply.

[FreelanceReinh]

Hi @ashish112,

Don't make the same mistakes twice:

1. In one of his earlier posts RW9 advised you to write ...memname='IMPORT3' ... -- you wrote 'import3' again in your latest program.
2. You omitted the GUESSINGROWS statement again in the PROC IMPORT step reading data2.csv.

[ashish112]

oh yes, It worked.

Guessingrows= max;

thank you so much and sorry for the mess up.