In fact, my 50+ tables represents some the 50+ last months. Of course, I have more observations each month the file is generated. What I want is to define limits from the past 50+ months usings statistics of those months to verify that my new data (next months) are within those limits. So the answer to your question is the a specific statistic on one variable (value). If it's not clear, do not hesitate. ... View more

I'll try to do that. Thanks Paige 🙂 ... View more

@novinosrin wrote: HI @jpprovost   I think   1. Append 50 tables to 1 2. Data step/proc summary (By groups)   should be a better approach than proc sql(which warrants 48 union all, or a macro loop one by one)   Hi @novinosrin ,   I did the following macro : %macro dash; %do i = 1 %to 3; PROC SQL; CREATE TABLE DASHBOARDk&i AS ( SELECT AVG(VALUE) AS MOYENNE, MAX(VALUE) AS MAXIMUM, MIN(VALUE) AS MINIMUM, SUM(VALUE) AS SOMME, COUNT(*) AS NBOBS FROM TEST0&i ); QUIT; run ; %end; %mend dash; It extracts the wanted informations into 50+ different tables. Now, I struggle to get them all together.   How can I use your proposed solution? I'm new with SAS and I'm trying to "Google" as many things as I can.   Thanks! ... View more

@PaigeMiller wrote: I have the number of obs (nobs) stocked in a dataset DATA1. I have 62 observations and I want to know if the 63rd is valid within the predicted values.   Sounds like a problem perfect for PROC SHEWHART.   Hi there,   It seems that I don't have this PROC.   ERROR: Procedure SHEWHART not found.   😞 ... View more

@ballardw wrote:   This is one piece of great resources you just gave to me. Thank you alot. I will use this for sure. But the problem that I have now is that all my files are .txt files and I don't know how to "load" them all into SAS. I thought that once all loaded, I could use VTable and VColumn to do the stuff that I want to. I will continue to seek for information about loading many files with "common" names (they are all dated and recurrent, so...).   By the way, I find it amazing that SAS does all of this in the background! If you haven't read the data into SAS then you can prevent most of the likely errors by reading them properly. Hopefully you have some document that shows what one of these text files should look like as far as column names, variable types, values (date and expected date format, currency, or similar), maximum length of character values and order in the file. Then READ the data with those rules. If you have such a document you might share it with the forum and we can show how to turn it into a data step to read the data. I agree with you. I have a specific SAS code for those files to load correctly. My goal is to validate that the last file produced is "as good as the last ones". I don't know if I'm "clear" enough... ... View more

@VDD wrote: basic way to read a text file is filename text "\\&path.\&file."; data read_text; infile text; *dlm="09"x; input var1 var2 var3; run; Yes, I already have my code (LOADTXT.sas) especially for those .txt files.   However, I have the list of filenames with their extension into a dataset and now I am looking to import thoses files with a macro using my code LOADTXT.SAS. I am struggling to pass into the macro to tell it to load each filename in each row.   By example, the dataset containing all the filenames from my folder looks like :   OBS FILEVAR 1 file1.txt 2 file2.txt 3 fileaza.txt ... ... ... The macro looks like this :   %macro loop; %do i = 1 %to 3/*&Total*/; DATA &&name&i ; infile "&path\&&filevar&i" MISSOVER LRECL = 153 ; input @1 FIELD1 10. @11 FIELD12 $15. @26 FIELD13 $1. @27 FIELD14 $1. @28 FIELD15 $2. @30 FIELD16 $6. @36 FIELD17 $60. @96 FIELD18 $2. @98 FIELD18 9. @107 FIELD10 13. @120 FIELD111 11. @131 FIELD1211 12. @143 FIELD122 $10. @153 FIELD133 $1. @154 FIELD144 $3. ; run ; %end; %mend loop; The place where I struggle is there :   infile "&path\&&filevar&i"   The &path is good, the &filevar also (filevar is the name of the column in the dataset where my files are listed). However, I am not able to tell the macro to load the filename that the row i contains.   Any ideas? ... View more

@ballardw wrote: @jpprovost wrote: I didn't know that SAS have a COMPARE procedure. Wow. However, my goal is to collect datas from all the files from past years and to verify that the new one is "clean". Is there a way to do that?   Could I create a macro that load all the files into one dataset and then use PROC COMPARE to compare between "all_past_files" and "latest files" ? I am not sure that such a thing would work since one set would be larger than the other...? No need for a "macro" to create such. SAS already does that in the background for you. They are data views in the SASHELP library such as Vtable, with information about datasets and views, Vcolumn with information about variables. These can also be accessed in Proc SQL as Dictionary Tables.   For instance one way to get information about all data sets in a library  is: (replace YOURLIB with the uppercase name of your library of interest) Proc sql; create table work.libtables as select * from dictionary.tables where libname='YOURLIB' ; run; The resulting data set will have the data set library, name, creation and modified dates, number of observations, number of variables, information about file size, deleted observations and some other somewhat esoteric characteristics. For your purpose the dates, number of variables and number of observations are likely the most useful, especially if the number of variables changes. The Vcolumns would be checked to see if the variables stay consistent for properties. Replace YOURLIB with the uppercase spelling of a library of interest in the following code. this creates a table that indicates the variable name and type on the row, the column of data set name and an X to indicate which. If you have a variable with multiple types you can determine which data sets it changes in. You could restrict the data sets or variables to consider in the WHERE statement or create a data set from the SASHELP.VCOLUMN or Dictionary.Columns and filter as desired. proc format library=work; value xmark 1-high='X' other =' ' ; run; Proc tabulate data=sashelp.vcolumn; where libname='YOURLIB'; class name type memname; table name*type, memname*n=' '*f=xmark. /misstext=' ' ; run;   This is one piece of great resources you just gave to me. Thank you alot. I will use this for sure. But the problem that I have now is that all my files are .txt files and I don't know how to "load" them all into SAS. I thought that once all loaded, I could use VTable and VColumn to do the stuff that I want to. I will continue to seek for information about loading many files with "common" names (they are all dated and recurrent, so...).   By the way, I find it amazing that SAS does all of this in the background! ... View more

I didn't know that SAS have a COMPARE procedure. Wow. However, my goal is to collect datas from all the files from past years and to verify that the new one is "clean". Is there a way to do that?   Could I create a macro that load all the files into one dataset and then use PROC COMPARE to compare between "all_past_files" and "latest files" ? I am not sure that such a thing would work since one set would be larger than the other...? ... View more

But the informat it's not what we want as an output, but it tells SAS to read the text from a specific INFORMAT and to convert it into value. Right? ... View more

@Tom wrote: @jpprovost wrote: I am curious to know why you add this to the date? +'30DEC1899'd After doing it, it goes (example) from 43004 to 26SEP2017. Here's the code I type : DATA test1; SET test; real_date=input(DATE,32.)+'30DEC1899'd ; format real_date date9.; run; Also, this is what I did this morning as a test :   DATA test1; SET test; real_date=input(DATE,yymmdd10.) ; run; And it gives me a lot of "." (missing values). I struggle to understand why? SAS and Excel have different ideas about whether to start counting from 1 or 0.  Also Excel thinks 1900 was a leap year.  That is why you should use 30DEC1899 instead of 01JAN1900.   To your second question, you told SAS to interpret DATE as if it had strings that look like '2016-12-30' (or '2016/12/30' or '20161230' etc. anything that the YYMMDD informat can recognize.).  But before you said it had strings that look like '30-12-2016' or '12-30-2016' or '43004'.  None of those are going to work with the YYMMDD informat.  If you are getting errors in the log then you should also see in the log some of the values of DATE that generated the error.   Try this: data test1; set test; if length(date)=5 then real_date=input(DATE,32.)+'30DEC1899'd ; else real_date=input(DATE,mmddyy10.) ; format real_date date9.; run; Oh gosh, it makes so much sense now. I understand that if I use put(source, format), the second argument will be the "output" format. If I use input(source, informat), the second argument is the "input" (or read) format. Am I wrong? ... View more

@Kurt_Bremser wrote: Excel stores dates as counts of days, starting with day 1 on 1900-01-01. But Lotus 1-2-3 (from which Excel was mostly copied) made the mistake of thinking that 1900 was a leap year, which it wasn't. So all dates in Excel (as MS happily perpetuates the error) between 1900-01-01 and 1900-03-01 are calculated incorrectly. So we have to assume day zero not as the last day of 1899, but the next-to-last day when we get data from Excel.   Note that LibreOffice Calc and other spreadsheet programs have corrected the mistake regarding the leapyear and do also use 1899-12-30 as day 0. That's why they also allow dates before that, as they can correctly calculate them. Sir,   This is a very very useful info that I just learn from you today.   If I convert my Excel file into a CSV file, will I have the same struggle with the date? (Just curious) ... View more