topic Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets in SAS Enterprise Guide

Wha'ts the best way to add and populate the row number into thousand of existing datasets

alepage — Tue, 23 Jul 2024 13:23:28 GMT

Hello,

I would like to know, what's the best way to add the variable rownumber = _n_ and to populate this variable in thousand of existing dataset.

I could use something similar to that snippet:

data want;
rowNo = _N_;
set sashelp.class;
run;

so, 

libname dest1 spde 'path1/co/...';
libname src1 spde 'path1/co/...';

data dest1.fnamex;
rowNo=_N_;
set src1.fnamex;
run;

Do a file listing and use that information to make a call execute.

If you have more efficient way to do that, please let me know

Regards,

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

ballardw — Tue, 23 Jul 2024 13:48:05 GMT

I would say the "best" way is to first answer why do thousands of existing datasets need to be replaced this way?

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

Tom — Tue, 23 Jul 2024 15:28:11 GMT

Let's suspend judgement of whether this is a wise thing to do.

The easiest way is to get the list of IN/OUT pairs in a dataset and use it to generate the code.

So if you just want to copy every dataset in libref SRC1 to the libref DEST1 then you could use a simple PROC CONTENTS to get the list of datasets.

So your program could look something like this:

libname dest1 spde 'path1/co/...';
libname src1 spde 'path1/co/...';

proc contents data=src1._all_ noprint out=contents; run;
filename code temp;
data _null_;
  set contents;
  by memname;
  if first.memname;
  put 'data dest1.' memname ';'
    / 'rowNo=_N_;'
    / 'set src1.' memname ';'
    / 'run;'
  ;
run;

%include code / source2;

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

alepage — Wed, 24 Jul 2024 13:48:08 GMT

it is a good question. Due to a new legislation, we need to remove policies transactions from the original dataset when the policies is older than 10 years. So if in a dataset, we have policies older than 10 years, their transactions need to be remove from the original dataset and put into another dataset, where some information about those policies will be depersonalized.

So we need to add the rownumber into the original dataset to keep the original observations rownumber either into the new dataset (observations removed) and the depersonalized dataset (observations removed from the original dataset) .

See the example below with sashelp.class

data class_original;
rownum=_N_;
set sashelp.class;
run;

/* suffix dpds= depersonnalized dataset */

data class class_dpds;
set class_original;
if rownum in (1,5,10,15) then 
do;
	Name='';
	output class_dpds;
end;
else
output class;
run;

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

alepage — Wed, 24 Jul 2024 14:00:32 GMT

You SAS code seems very interesting but I have made a test with sashelp and it is not working.

libname dest1 %sysfunc(quote(%sysfunc(pathname(work))));


proc contents data=sashelp._all_ noprint out=contents; run;
filename code temp;
data _null_;
  set contents;
  by memname;
  if first.memname;
  put 'data dest1.' memname ';'
    / 'rowNo=_N_;'
    / 'set sashelp.' memname ';'
    / 'run;'
  ;
run;

%include code / source2;

Could you please provide an example with sashelp

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

Tom — Wed, 24 Jul 2024 14:30:40 GMT

Works fine for me. Example processing just the first 4 datasets found.

1    proc contents data=sashelp._all_ noprint out=contents;run;

NOTE: The data set WORK.CONTENTS has 17124 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.16 seconds
      cpu time            0.17 seconds


2    filename code temp;
3    data _null_;
4      set contents ;
5      by memname;
6      if first.memname;
7      dsn+1;
8      if dsn>3 then stop;
9      file code;
10     put 'data work.' memname ';'
11       / 'rownum+1;'
12       / 'set sashelp.' memname ';'
13       / 'run;'
14     ;
15   run;

NOTE: The file CODE is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 12 records were written to the file (system-specific pathname).
      The minimum record length was 4.
      The maximum record length was 20.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


16   %include code / source2 ;
NOTE: %INCLUDE (level 1) file CODE is (system-specific pathname).
17  +data work.AACOMP ;
18  +rownum+1;
19  +set sashelp.AACOMP ;
20  +run;

NOTE: The data set WORK.AACOMP has 2020 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


21  +data work.AARFM ;
22  +rownum+1;
23  +set sashelp.AARFM ;
24  +run;

NOTE: The data set WORK.AARFM has 130 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


25  +data work.ADSMSG ;
26  +rownum+1;
27  +set sashelp.ADSMSG ;
28  +run;

NOTE: The data set WORK.ADSMSG has 426 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


NOTE: %INCLUDE (level 1) ending.

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

alepage — Wed, 24 Jul 2024 14:35:33 GMT

file code is not present into your data _null_ ..... run; portion of your sas code. It is why it was not working

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

Tom — Wed, 24 Jul 2024 14:37:04 GMT

@alepage wrote:
file code is not present into your data _null_ ..... run; portion of your sas code. It is why it was not working

Yes. The data step will know you want to write to a file unless you tell it.

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

alepage — Wed, 24 Jul 2024 14:39:56 GMT

Last question: In a library, it is possible to trace only the spde file using proc contents and if so which variable and value.

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

Tom — Wed, 24 Jul 2024 14:44:21 GMT

I library (really a libref) only has one engine. You should be able to tell the SPDE librefs by looking at the ENGINE variable in the PROC CONTENTS output.

Now a directory on the disk can have multiple types of files. You can use SAS version 6 and SAS version 7 (the current format) in the same directory, but the engine being used by the libref will determine which files SAS actually "sees" with that libref.

Re: Wha'ts the best way to add and populate the row number into thousand of existing datasets

verdantsphinx — Wed, 24 Jul 2024 14:46:20 GMT

Add "file code;" before

if first.memname;