BookmarkSubscribeRSS Feed
santosh_pat69
Quartz | Level 8

Hi All,

I need to read historic XLSX,Xls files, we are having some data missing in our database tables.

As the files were updated and we are not sure if we have loaded the updated files on to the Tables.

 

We recieve a file from a vendor on monthly basis in .xlsx format (

xxxxxxx xxxxxxx-xxxxxxx_YYMM_cmb.xlsx)  and as the program is designed to import .xls and we dont want to distrub the existing set up. so we are just converting the monthly.XLSX file to .xls. Whenever the Input file .xlsx is updated we recieve a New file with a new name (xxxxxxx xxxxxxx-xxxxxxx_YYMM_cmb_upd_v2.xlsx) if there are more than one update to the file we recieve the files with a increment in value 'Vn'.

 

Now we need ot create a Master dataset which would have all the correct records from the input .xlsx file.

i have already designed a program which woul read all the .xlsx files from the desired location and created a dataset for each of the input .xlsx files and later append all the datasets to a Master file.

 

Datasets created from the .xlsx input files for example:

 

1.Sales_201401_cmb

2.Sales_201402_cmb

3.Sales_201403_cmb

 

the above files are appended to a Master dataset "Master_Sales"

This works fine with the input files which donot have updated Versions.

Now i need to compare the Number of records from Version V1 and V2 and append the dataset with Highest number of records in V1 or V2.

 

1.Sales_201401_cmb_v1

2.Sales_201402_cmb_v2

3.Sales_201403_cmb_v3.

 

Kinly Suggest me on this.

 

5 REPLIES 5
Reeza
Super User

Sashelp.vtable contains metadata about your tables which includes the number of records. You can use that to find the table with highest number of records. 

santosh_pat69
Quartz | Level 8

Hi Reeza

 

Thank you for your Quick response,

data _null_;
 set SASHELP.Vtable;
 where libname="WORK" and memname="MSTR_DM_&I";
 call symput("count_obs",nlobs);
run;

 

But here i need to compare the comunt between to files Version1 and Version for the file name V1,V2 and append the dataset with highest count to the master dataset.

 

In my current code it chks for the Xls,xlsx files reads it and appends it without checking the counts.

now  i have check the counts and append the dataset for the highest count from file V1 and V2.

 

for example :

 

XXXXXX_XXXXXX_XXX_YYMM_Cmb.xlsx ===>>>> 400 counts.

XXXXXX_XXXXXX_XXX_YYMM_Cmb_V2.xlsx ===>>>> 410 counts.

XXXXXX_XXXXXX_XXX_YYMM_Cmb_V3.xlsx ===>>>> 455 counts.

i need to read all these files take the counts compare the file names and counts  then append the dataset for the file with max number of records.

 

 

 

Reeza
Super User

So, you've imported all the files. 

 

Use the table to get the number of observations and take the largest one.

 

I don't know enough about your process to know where you want to do this, but it is a trivial issue to determine the largest one. See the example below.

 

 

proc sql;
creeate table table_obs as 
select memname, nlobs
from sashelp.vtable
where libname='WORK' and memname like 'MSTR_DM_%'
order by nlobs descending;
quit;

data _null_;
set table_obs;
if _n_=1 then call symput('table_to_use', memname);
run;

%put &table_to_use.;

 

 

santosh_pat69
Quartz | Level 8

Hi Reeza,

Thank you !!!

My code :- It detemines all the .xlsx and Xls file

 

data _Null_;

Call system("find &filedir1. . \( -name \*.xls -o -name \*.xlsx \) -print> /Santosh/exls.csv");

run;

 

The out put of hte above will have a list of all hte xlsx,xls files, my code reads all the files listed sequentially, but now i need to compare the files v,v1 and v2 and append only hte dataset which has the highest count to the Master data set and should also compare the names of the files used.

 

Sales_Detls_201607.xls

Sales_Detls_201607_Upd_V1.xls

Sales_Detls_201607_Upd_V2.xls

 

for example we recieved three files for any month now after the datasets are checked for the record how do i append the dataset containing highest records for the same period of time.

 

 

santosh_pat69
Quartz | Level 8

Hi,

 

Thank you Reeza!!!

 

I have taken the count of the records from the interim datasets, and then added the tables who have max number of records into the tables.

 

Thank you for your help.

 

 

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1129 views
  • 0 likes
  • 2 in conversation