topic make the same changes in many datasets in SAS Programming

make the same changes in many datasets

fengyuwuzu — Tue, 07 Feb 2017 20:28:28 GMT

I have a bunch of raw datasets, but for one subject, it has two IDs.

I want to change the wrong ID to the correct one, and generate another set of raw data.

Is there an efficient way to do this change without running it many times for each dataset?

data newlib.right_ID;
set raw.wrong_ID;
if ID="A0001" then ID='B0001";
run;

Re: make the same changes in many datasets

anoopmohandas7 — Tue, 07 Feb 2017 20:33:36 GMT

Did you think of Macro's to achieve the outcome ?

Re: make the same changes in many datasets

fengyuwuzu — Tue, 07 Feb 2017 20:35:13 GMT

yes, a macro and loop through list

Re: make the same changes in many datasets

art297 — Tue, 07 Feb 2017 20:39:11 GMT

Do you need to do that for all of the datasets in the RAW library?

Art, CEO, AnalystFinder.com

Re: make the same changes in many datasets

fengyuwuzu — Tue, 07 Feb 2017 20:46:23 GMT

not all, only those with wrong ID

Re: make the same changes in many datasets

Reeza — Tue, 07 Feb 2017 21:09:52 GMT

Do you have a dataset with the wrong ID's?

How many datasets and do they have a naming convention?

@fengyuwuzu You've posted enough to know to post sample data, so can I assume you're only asking for methodological help here?

You can create a format and apply it to all your datasets so that it 'shows' as correct or manually loop through and reassign. You can't reassign using a proc datasets so that means recreating your data. If you use a format, you can apply it using proc datasets and not recreate all your datasets. Depending on the size of datasets this may be a factor in the solution you choose.

It seems easier to create a format and use as you go as well, especially if changes occur over time.

Re: make the same changes in many datasets

art297 — Tue, 07 Feb 2017 21:11:00 GMT

Do you have a file that contains a list of those file names?

Re: make the same changes in many datasets

art297 — Tue, 07 Feb 2017 21:19:33 GMT

Assuming you have a file that contains a list of those files, here is one way to do it:

libname raw 'c:\art\test';
libname newlib 'c:\art\out';

/*proc sql noprint;*/
/*  select file  into: files separated by '*'*/
/*    from dictionary.tables*/
/*      where libname='RAW'*/
/*  ;*/
/*quit;*/

data filelist;
  informat file $32.;
  input file;
  cards;
test1
test2
test3
;

proc sql noprint;
  select file  into: files separated by '*'
    from work.filelist
  ;
quit;

%macro doit;
  %let i=1;
  %do %while (%scan(&files.,&i.,*) ne );
    data newlib.%scan(&files.,&i.,*);
      set raw.%scan(&files.,&i.);
      if ID="A0001" then ID="B0001";
    run;
    %let i=%eval(&i.+1);
  %end;
%mend doit;
%doit

Note that I commented out the section that would have accomplished the task for all of the files in the raw directory.

HTH,

Art, CEO, AnalystFinder.com

Re: make the same changes in many datasets

anoopmohandas7 — Tue, 07 Feb 2017 22:58:26 GMT

Can you explain 'ne' in the while loop.
%while (%scan(&files.,&i.,*) ne );

Thanks.

Re: make the same changes in many datasets

art297 — Tue, 07 Feb 2017 23:04:22 GMT

Simply a way of defining a where condition to keep going until nothing is found