Sometimes it is needed to scan a table in order to get the correct number of observations from a table. This can for example be in a database, where SAS dictionary tables do not know the number of observations. To scan easily, the below code can assist.
This is based on the paper: http://www2.sas.com/proceedings/sugi26/p095-26.pdf
where more information about this topic exists.
For this example, an XLXS table with random numbers is made, and the code should find its number of observations.
The XLXS engine may use the SAS module Access to PC file formats.
This code is made in SAS 94M5, but runs on lower Maintenance levels in SAS94.
First the dummy data is made, then the code itself is scanning the table, and at the end it is put into a macro for reuse.
** Step 1: Create libname and data..;;
** Options and macro vars..;; %let num_tags = 100; ** Number of tags to create...;; %let num_row_tags = 10; ** Number of rows per tag to create..;;
** libname to excel spreadsheet that is created...;; libname d_tmp XLSX 'D:\Temp\Temp\RAW_DATA.xlsx'; ** Using excel sheet named raw_data and file raw_data.xlsx as source..;;
** Making dummy data.. Having 100 TAGS and 10 rows per tag, should give 1000 rows in total. 1 hardcoded DATETIME value and random values ;;
data d_tmp.raw_data; length TAG $64. Timestamp 8 Value 8 Status $32.; do i=1 to &num_tags.; tag = cat("Tag", i); timestamp = "01oct2017:00:00:00"dt; status = "OK"; do y=1 to %eval(&num_row_tags. -1); ** Minus 1 due to the DO-loop iteration logic..;; value = ranuni(0); output; end; output; end; drop i y; run;
** Step 2: Check with proc contents for number of observations..;;
** Using proc content to see number of observations. This will give a missing value for number of rows..;; proc contents data=d_tmp.raw_data; run; quit;
** Step 3: Use a scan of the table to find the real rumber of observations..;;
** Find number of observations ny scanning the table.. NOTE: This may be time expensive for large tables, but it will give correct number..;; data _null_; dsid = open("d_tmp.raw_data", 'is'); do while (fetch(dsid, 'noset') = 0); i + 1; end; put i=; rc = close(dsid); call symput("num", i); stop; run;
%put NOTE: From datastep: Tags in dataset: &num_tags. Number of rows per tag: &num_row_tags. Number of observations in dataset: &num.;
** Step 4. Create a reusable macro..;;
** This can then become a simple macro code for re-use..;; %macro find_num_obs (dataset, macr_nm); ** Find number of observations ny scanning the table.. NOTE: This may be time expensive for large tables, but it will give correct number..;; %global ¯_nm;
data _null_; dsid = open("&dataset.", 'is'); do while (fetch(dsid, 'noset') = 0); i + 1; end; put i=; rc = close(dsid); call symput("Macro_var_nm", i); stop; run; %Mend find_num_obs ;
** USAGE: parameter 1: Name of dataset Parameter 2: Name of resulting macrovar. holding number of obs. in dataset ;; %find_num_obs (%str(d_tmp.raw_data), Macro_var_nm );;
%put NOTE: From Macro: Tags in dataset: &num_tags. Number of rows per tag: &num_row_tags. Number of observations in dataset: &Macro_var_nm.;
... View more
