BookmarkSubscribeRSS Feed
fengyuwuzu
Pyrite | Level 9

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;
9 REPLIES 9
anoopmohandas7
Quartz | Level 8
Did you think of Macro's to achieve the outcome ?
fengyuwuzu
Pyrite | Level 9

yes, a macro and loop through list 

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

fengyuwuzu
Pyrite | Level 9
not all, only those with wrong ID
Reeza
Super User

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.

art297
Opal | Level 21

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

art297
Opal | Level 21

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

 

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

Thanks.
art297
Opal | Level 21

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1543 views
  • 1 like
  • 4 in conversation