topic Re: Import modify export and set of files iteratively. in SAS Procedures

Import modify export and set of files iteratively.

nuts — Wed, 06 Nov 2013 16:11:36 GMT

Hi All, I am new to SAS and am stuck at a point and would appreciate help.

I need to import 20 files(path and name stored in a dataset), change the values in a particular column and then export them each with a specified name.I am not able to do this. Please help me out here.

Re: Import modify export and set of files iteratively.

data_null__ — Wed, 06 Nov 2013 16:27:51 GMT

Please specify the FILE TYPE. If they are text you may not need the IMPORT/EXPORT bits.

Re: Import modify export and set of files iteratively.

nuts — Wed, 06 Nov 2013 17:58:24 GMT

.csv it is

Re: Import modify export and set of files iteratively.

data_null__ — Wed, 06 Nov 2013 18:07:36 GMT

How do you identify the fields and values that need to be changed? If you summpy example data you will getter feeback but the basics are this.

data _null_;

set driver;

create new ouput file name from file name in driver.

infile dummy1 csv filevar=file-name-variable-from-driver end=eof;

file dummy2 csv filevar=new-file-name;

do while(not eof);

input.....;

something

put ....;

end;

run;

Re: Import modify export and set of files iteratively.

nuts — Thu, 07 Nov 2013 03:55:43 GMT

Hi,

I created a dataset with a single column(to_be_imported) which contains the path of those 20 .csv to be imported.

Now, I am stuck at this step:

for(i=1 to 20)

1. Import ith csv into a dataset say work. Edit a column. Export it back with the name xyz||i.csv into a specified folder.

2.i=i+i+1;

Re: Import modify export and set of files iteratively.

data_null__ — Thu, 07 Nov 2013 11:28:47 GMT

show your work.

Re: Import modify export and set of files iteratively.

nuts — Thu, 07 Nov 2013 14:16:21 GMT

Hi,

Thanks for being patient. I succeeded with the part of importing them all one by one but I have a new trouble now. In the highlighted step, VAR2 column is getting

replaced but its truncated. Eg: If value_after_editing = HANDWASH, only HANDWA is coming in VAR2 column. It is truncating after 6/7 chars. I tried INFORMAT but then column becomes empty. Help please!

%MACRO edit_file(dataset, value_after_editing);

data &dataset;

set &dataset;

VAR2= "& value_after_editing";

run;

%mend edit_file;

%MACRO SCANLOOP(SCANFILE,FIELD1,FIELD2,FIELD3);

/* First obtain the number of */

/* records in DATALOG */

DATA _NULL_;

IF 0 THEN SET &SCANFILE NOBS=X;

CALL SYMPUT('RECCOUNT',X);

STOP;

RUN;

/* loop from one to number of */

/* records */

%DO I=1 %TO &RECCOUNT;

/* Advance to the Ith record */

DATA _NULL_;

SET &SCANFILE (FIRSTOBS=&I);

/* store the variables */

/* of interest in */

/* macro variables */

CALL SYMPUT('csv_to_imported',&FIELD1);

CALL SYMPUT('dataset_name_After_importing',&FIELD2);

CALL SYMPUT('Edited_value_in_column_2',&FIELD3);

STOP;

RUN;

/* now perform the tasks that */

/* wish repeated for each */

/* observation */

%import_file(&csv_to_imported, &dataset_name_After_importing);

%edit_file(&dataset_name_After_importing,&Edited_value_in_column_2);

%END;

%MEND SCANLOOP;

/* Call SCANLOOP */

%SCANLOOP(DATALOG,FILENM,FIELD2,FIELD3);

RUN;

Re: Import modify export and set of files iteratively.

ballardw — Thu, 07 Nov 2013 16:00:45 GMT

The truncation in your example likely means the the original dataset when created is creating var2 with a length of $ 6. You may need to consider how you are creating the datasets to ensure your variables are long enough to contain the desired change values.

Re: Import modify export and set of files iteratively.

nuts — Thu, 07 Nov 2013 16:13:47 GMT

No. I checked in the original dataset. It has complete names. It's only when I pass that in a macro, it gets truncated. What to do?

Re: Import modify export and set of files iteratively.

nuts — Thu, 07 Nov 2013 17:07:18 GMT

But I will recheck and post.Also wanted to ask if it is possible to replace an excel with an exported excel of different name?