topic Delete the second observation in SAS Programming

Delete the second observation

maxjiang6999 — Mon, 07 Oct 2019 23:26:00 GMT

%macro clean();
%do i =1 %to 8;
data d_clean&i;
set dsn&i;
%if _n_ = 1 %then %delete;
%run;
%end;
%mend clean;

%clean()

I have a bunch of CSV files with similar names. After I read them in, I want to get rid of the second observations from those files. I wrote the macro (pasted above, which was hoped to do the loop work pasted below) to do it. However, I didn't get my result. Can anybody help?

data clean_1; set dsn1; if _n_ = 1 then delete;run;
data clean_2; set dsn2; if _n_ = 1 then delete;run;
data clean_3; set dsn3; if _n_ = 1 then delete;run;
.....

Thanks,

Re: Delete the second observation

Shmuel — Tue, 08 Oct 2019 01:06:40 GMT

If I understand you then

if _n_ = 1 then delete;

means to delete the first row. Replace the 1 into 2 in order to delete the second row in the file.

If I'm wrong - please explain' what do you mean by " I didn't get my result." ? and what issues did you have ?

Re: Delete the second observation

Patrick — Tue, 08 Oct 2019 01:50:28 GMT

@maxjiang6999 wrote:
....
I have a bunch of CSV files with similar names. After I read them in, I want to get rid of the second observations from those files.
....

Then why do you read this 2nd line from the .csv into SAS at all? Why not skip it while reading instead of post processing your SAS tables.

If you provide a bit more detail what you have and what you need then I'm certain there will be a solution.

Re: Delete the second observation

hashman — Tue, 08 Oct 2019 03:08:29 GMT

@maxjiang6999:

There's no need to completely reread and rewrite each file just to delete one and the same observations from each. Simply modify the original files to mark the observations you want for deletion. When you reference thus modified files downstream for reading, only the non-marked observations will be read.

data dsn_1 dsn_2 dsn_3 ;                                                                                                                                                                                                                                        
  do key = 1 to 5 ;                                                                                                                                                                                                                                             
    data = key * 1001 ;                                                                                                                                                                                                                                         
    output ;                                                                                                                                                                                                                                                    
  end ;                                                                                                                                                                                                                                                         
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
%macro clean (N_del=, prefix=, N_files=) ;                                                                                                                                                                                                                      
  %local i ;                                                                                                                                                                                                                                                    
  %do i = 1 %to &N_files ;                                                                                                                                                                                                                                      
    data &prefix&i ;                                                                                                                                                                                                                                            
      p = &N_del ;                                                                                                                                                                                                                                              
      modify &prefix&i point = p ;                                                                                                                                                                                                                              
      remove ;                                                                                                                                                                                                                                                  
      stop ;                                                                                                                                                                                                                                                    
    run ;                                                                                                                                                                                                                                                       
  %end ;                                                                                                                                                                                                                                                        
%mend ;                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                
%clean (N_del=2, prefix=dsn_, N_files=3)                                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                
/* just checking */                                                                                                                                                                                                                                             
data _null_ ;                                                                                                                                                                                                                                                   
  set dsn_1 dsn_2 dsn_3 ;                                                                                                                                                                                                                                       
run ;

As you'll see in "just checking", the observations marked for deletion by the macro aren't read in.

Kind regards

Paul D.

Re: Delete the second observation

ballardw — Tue, 08 Oct 2019 15:48:09 GMT

@maxjiang6999 wrote:
%macro clean();
%do i =1 %to 8;
data d_clean&i;
set dsn&i;
%if _n_ = 1 %then %delete;
%run;
%end;
%mend clean;

%clean()
I have a bunch of CSV files with similar names. After I read them in, I want to get rid of the second observations from those files. I wrote the macro (pasted above, which was hoped to do the loop work pasted below) to do it. However, I didn't get my result. Can anybody help?
data clean_1; set dsn1; if _n_ = 1 then delete;run;
data clean_2; set dsn2; if _n_ = 1 then delete;run;
data clean_3; set dsn3; if _n_ = 1 then delete;run;
.....
Thanks,

Prevent don't fix: Read the data files with FIRSTOBS=3.

Unless you are trusting proc import to read multiple files as needed.