DATA Step, Macro, Functions and more

make the same changes in many datasets

Reply
Super Contributor
Posts: 318

make the same changes in many datasets

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;
Contributor
Posts: 44

Re: make the same changes in many datasets

Posted in reply to fengyuwuzu
Did you think of Macro's to achieve the outcome ?
Super Contributor
Posts: 318

Re: make the same changes in many datasets

Posted in reply to anoopmohandas7

yes, a macro and loop through list 

PROC Star
Posts: 7,474

Re: make the same changes in many datasets

Posted in reply to fengyuwuzu

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

 

Art, CEO, AnalystFinder.com

Super Contributor
Posts: 318

Re: make the same changes in many datasets

not all, only those with wrong ID
Super User
Posts: 19,815

Re: make the same changes in many datasets

Posted in reply to fengyuwuzu

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.

PROC Star
Posts: 7,474

Re: make the same changes in many datasets

Posted in reply to fengyuwuzu

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

PROC Star
Posts: 7,474

Re: make the same changes in many datasets

Posted in reply to fengyuwuzu

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

 

Contributor
Posts: 44

Re: make the same changes in many datasets

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

Thanks.
PROC Star
Posts: 7,474

Re: make the same changes in many datasets

Posted in reply to anoopmohandas7

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

Ask a Question
Discussion stats
  • 9 replies
  • 168 views
  • 1 like
  • 4 in conversation