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.
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.;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.